order_import_procedure
xxfilix_di_intf_proc ( p_period_date DATE, --p_period_time VARCHAR2, p_order_type VARCHAR2, p_price_name VARCHAR2, p_ship_to_org_id VARCHAR2, --p_order_number VARCHAR2, p_file_name VARCHAR2, p_CUSTOMER_NUMBER varchar2, p_ORDER_SOURCE_ID varchar2, p_session_id number, p_org_id number, p_vendor_id varchar2)IS CURSOR c1 IS SELECT --vendor_id, period_date, period_time, --item_code, rtrim(ltrim(item_id)) item_id, quantity, un_loc, --us_loc, nagare_no, pds_number FROM XXFILIX_DI_STAGING WHERE period_date =p_period_date---> TO_DATE (p_period_date, 'dd/mon/yyyy') ---and session_id=p_session_id and org_id=p_org_id and ship_to_org_id=p_ship_to_org_id and vendor_id=p_vendor_id order by period_time desc ; ----------------------------------------------------- CURSOR c2 IS SELECT --period_date, period_time, pds_number, count(period_time) FROM XXFILIX_DI_STAGING WHERE period_date =p_period_date---------------> TO_DATE (p_period_date,'dd/mon/yyyy') ---and session_id=p_session_id and org_id=p_org_id and ship_to_org_id=p_ship_to_org_id and vendor_id=p_vendor_id group by period_date,period_time,pds_number order by period_time desc; ------------------------------------ CURSOR c4 IS SELECT period_date, period_time, pds_number, count(period_time) FROM XXFILIX_DI_STAGING WHERE period_date =p_period_date---> TO_DATE (p_period_date, 'dd/mon/yyyy') and pds_number is not null ---and session_id=p_session_id and org_id=p_org_id and ship_to_org_id=p_ship_to_org_id and vendor_id=p_vendor_id group by period_date,period_time,pds_number order by period_time desc; ------------------------------------ CURSOR C3 IS SELECT * FROM xxfilix_di_staging WHERE period_date =p_period_date and trans_to_iface='Y' --and session_id=p_session_id and org_id=p_org_id and ship_to_org_id=p_ship_to_org_id and vendor_id=p_vendor_id; in_cnt number; v_time varchar2(15);BEGINfor j in c2 loop BEGIN INSERT INTO oe_headers_iface_all ( --BLANKET_NUMBER, SHIP_TO_ORG_ID, --ATTRIBUTE1, order_source_id, orig_sys_document_ref, --org_id, ordered_date, order_type, price_list, transactional_curr_code, ---salesrep_id, --accounting_rule_id, --payment_term_id, --ship_from_org_id, customer_number, booked_flag, created_by, creation_date, last_updated_by, last_update_date, operation_code --SALES_CHANNEL_CODE, --SALES_CHANNEL ) VALUES ( --p_order_number, p_ship_to_org_id, --p_file_name, p_ORDER_SOURCE_ID, xxfilix_load_header.NEXTVAL, --83,-----------hr_operating_units SYSDATE, p_order_type, p_price_name, 'INR', -----3, --1, --4, --85, p_CUSTOMER_NUMBER, 'N', -1, SYSDATE, -1, SYSDATE, 'INSERT' --p_period_date||' '||substr(to_char(to_date(p_period_time,'HH24:MI'),'dd/mm/yyyy HH24:MI'),12), --'INDIRECT' ); COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line ( 'can not be inserted in Header Interface Table' || SQLCODE || SUBSTR (SQLERRM, 150) ); fnd_file.put_line (fnd_file.LOG, 'pin table' || SQLERRM); ROLLBACK; END; FOR i IN c1 LOOP if nvl(j.period_time,0) =nvl(i.period_time,0) then if nvl(rtrim(ltrim(j.pds_number)),0)=nvl(rtrim(ltrim(i.pds_number)),0) then BEGIN INSERT INTO oe_lines_iface_all (order_source_id, orig_sys_document_ref, orig_sys_line_ref, orig_sys_shipment_ref, --org_id, --inventory_item_id, -- INVENTORY_ITEM, customer_item_name, ordered_quantity, --order_quantity_uom, shipped_quantity, ---ship_from_org_id, --ship_to_org_id, ---invoice_to_org_id, ---payment_term_id, request_date, created_by, creation_date, last_updated_by, last_update_date, schedule_ship_date, CUST_PRODUCTION_SEQ_NUM, operation_code, ATTRIBUTE1, ATTRIBUTE2, CUSTOMER_PRODUCTION_LINE, --ATTRIBUTE11, CUSTOMER_JOB, ATTRIBUTE15 ) VALUES (p_ORDER_SOURCE_ID, xxfilix_load_header.CURRVAL, xxfilix_load.NEXTVAL, xxfilix_load.NEXTVAL, --83, --i.item_code, rtrim(ltrim(i.item_id)), i.quantity, --'NOS', NULL, --85, --p_ship_to_org_id, ---1206, --4, SYSDATE, -1, SYSDATE, -1, SYSDATE, i.period_date, i.period_date, 'INSERT', i.nagare_no, --replace(nagare_no,chr(13),''), p_file_name, i.un_loc, i.period_time, i.pds_number --i.PERIOD_TIME ---i.PERIOD_DATE||' '||substr(to_char(to_date(i.PERIOD_TIME,'HH24:MI'),'dd/mm/yyyy HH24:MI'),12) ); COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line ( 'can not be inserted in LinesInterface Table' || SQLCODE || SUBSTR (SQLERRM, 150) ); fnd_file.put_line (fnd_file.LOG, 'pin table' ||SQLERRM); ROLLBACK; END; end if; end if; --v_time:=null; END LOOP; end loop; -------------------------------------- update XXFILIX_DI_STAGING set trans_to_iface='Y' WHERE period_date =p_period_date AND transfer_status = 'Y' --and session_id=p_session_id and org_id=p_org_id and ship_to_org_id=p_ship_to_org_id and vendor_id=p_vendor_id; ----------------------------------- FOR I IN C3LOOP insert into XXFILIX_DI_SCHEDULE ( VENDOR_ID, PERIOD_DATE, PERIOD_TIME, ITEM_CODE, QUANTITY, UN_LOC, US_LOC, NAGARE_NO, pds_number, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, ATTRIBUTE16, ATTRIBUTE17, ATTRIBUTE18, ATTRIBUTE19, ATTRIBUTE20, ATTRIBUTE21, ATTRIBUTE22, ATTRIBUTE23, ATTRIBUTE24, ATTRIBUTE25, ATTRIBUTE26, ATTRIBUTE27, ATTRIBUTE28, ATTRIBUTE29, ATTRIBUTE30, ATTRIBUTE31, ATTRIBUTE32, ATTRIBUTE33, ATTRIBUTE34, ATTRIBUTE35, ATTRIBUTE36, ATTRIBUTE37, ATTRIBUTE38, ATTRIBUTE39, ATTRIBUTE40,ATTRIBUTE41,ATTRIBUTE42,ATTRIBUTE43,ATTRIBUTE44,ATTRIBUTE45,ATTRIBUTE46,ATTRIBUTE47,ATTRIBUTE48,ATTRIBUTE49,ATTRIBUTE50,ATTRIBUTE51,ATTRIBUTE52,ATTRIBUTE53,ATTRIBUTE54,ATTRIBUTE55,ATTRIBUTE56,ATTRIBUTE57,ATTRIBUTE58,ATTRIBUTE59,ATTRIBUTE60,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,ITEM_ID,TRANSFER_STATUS,REMARKS,FILE_PATH,PRICE_NAME,ORDER_TYPE,FILE_NAME,CUSTOMER_SHIP_TO,AGREEMENT_NAME,trans_to_iface ,session_id,org_id,org,ship_to_org_id)values(I.VENDOR_ID,I.PERIOD_DATE,I.PERIOD_TIME,I.ITEM_CODE,I.QUANTITY,I.UN_LOC,I.US_LOC,I.nagare_no,---replace(nagare_no,chr(13),''),I.pds_number,I.ATTRIBUTE1,I.ATTRIBUTE2,I.ATTRIBUTE3,I.ATTRIBUTE4,I.ATTRIBUTE5,I.ATTRIBUTE6,I.ATTRIBUTE7,I.ATTRIBUTE8,I.ATTRIBUTE9, I.ATTRIBUTE10,I.ATTRIBUTE11,I.ATTRIBUTE12,I.ATTRIBUTE13,I.ATTRIBUTE14,I.ATTRIBUTE15,I.ATTRIBUTE16,I.ATTRIBUTE17,I.ATTRIBUTE18,I.ATTRIBUTE19,I.ATTRIBUTE20,I.ATTRIBUTE21,I.ATTRIBUTE22,I.ATTRIBUTE23,I.ATTRIBUTE24,I.ATTRIBUTE25,I.ATTRIBUTE26,I.ATTRIBUTE27,I.ATTRIBUTE28,I.ATTRIBUTE29,I.ATTRIBUTE30,I.ATTRIBUTE31,I.ATTRIBUTE32,I.ATTRIBUTE33,I.ATTRIBUTE34,I.ATTRIBUTE35,I.ATTRIBUTE36,I.ATTRIBUTE37,I.ATTRIBUTE38,I.ATTRIBUTE39,I.ATTRIBUTE40,I.ATTRIBUTE41,I.ATTRIBUTE42,I.ATTRIBUTE43,I.ATTRIBUTE44,I.ATTRIBUTE45,I.ATTRIBUTE46,I.ATTRIBUTE47,I.ATTRIBUTE48,I.ATTRIBUTE49,I.ATTRIBUTE50,I.ATTRIBUTE51,I.ATTRIBUTE52,I.ATTRIBUTE53,I.ATTRIBUTE54,I.ATTRIBUTE55,I.ATTRIBUTE56,I.ATTRIBUTE57,I.ATTRIBUTE58,I.ATTRIBUTE59,I.ATTRIBUTE60,I.CREATED_BY,I.CREATION_DATE,I.LAST_UPDATED_BY,I.LAST_UPDATE_DATE,rtrim(ltrim(I.ITEM_ID)),I.TRANSFER_STATUS,I.REMARKS,I.FILE_PATH, I.PRICE_NAME, I.ORDER_TYPE, I.FILE_NAME, I.CUSTOMER_SHIP_TO, I.AGREEMENT_NAME, i.trans_to_iface, i.session_id, i.org_id, i.org, i.ship_to_org_id); end loop; ------------------------------------------- delete FROM xxfilix_di_details WHERE period_date = p_period_date and TRANSFER='N' --and session_id=p_session_id and org_id=p_org_id and ship_to_org_id=p_ship_to_org_id and vendor_id=p_vendor_id; COMMIT;EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line ('can not insert' || SQLCODE || SUBSTR (SQLERRM, 150) ); ROLLBACK; raise_application_error (-20200, 'can not insert' || 'Error: ' || SUBSTR (SQLERRM, 150) ); fnd_file.put_line (fnd_file.LOG, 'others' || SQLERRM);END;/
