[Home] [Help]
39: as I.
40: Step 3: For every operating unit change, get the OPUNIT details.
41: If no detail found, set the transferred_to_oe_flag to 'E'
42: for those headers.
43: Step 4: insert into oe_headers_iface_all from po_requisition_headers
44: Step 5: For each row
45: insert into oe_lines_iface_all from po_requisition_lines
46: Step 6: Update the transferred_to_oe_flag to 'Y' for all 'I' ones and
47: to 'N' for all 'E' ones.
105: select nvl(org.operating_unit,-1),
106: hdr.requisition_header_id,
107: lin.requisition_line_id
108: from po_requisition_lines lin,
109: po_requisition_headers hdr,
110: org_organization_definitions org
111: where lin.requisition_header_id = hdr.requisition_header_id
112: and lin.source_organization_id = org.organization_id
113: and hdr.transferred_to_oe_flag = 'I'
137:
138: /* Bug 2630523 Performance of the following SQL will be good if it choose
139: Hash Join. So added the HASH_SJ hint to improve the performance */
140:
141: UPDATE PO_REQUISITION_HEADERS PRH
142: SET PRH.TRANSFERRED_TO_OE_FLAG = 'I'
143: WHERE nvl(PRH.TRANSFERRED_TO_OE_FLAG,'N') = 'N'
144: AND PRH.AUTHORIZATION_STATUS = 'APPROVED'
145: AND exists (select /*+ HASH_SJ */ 'At least one inventory sourced line'
153: Fnd_File.Put_Line(FND_FILE.LOG,to_char(SQL%ROWCOUNT)||' Reqs selected for processing');
154:
155: ELSE
156:
157: UPDATE PO_REQUISITION_HEADERS PRH
158: SET PRH.TRANSFERRED_TO_OE_FLAG = 'I'
159: WHERE nvl(PRH.TRANSFERRED_TO_OE_FLAG,'N') = 'N'
160: AND PRH.AUTHORIZATION_STATUS = 'APPROVED'
161: AND PRH.REQUISITION_HEADER_ID = p_req_header_id
229: l_ac_id,
230: l_ir_id);
231:
232: IF (l_error_flag = 'Y') then
233: UPDATE PO_REQUISITION_HEADERS
234: SET TRANSFERRED_TO_OE_FLAG = 'E'
235: WHERE REQUISITION_HEADER_ID = l_req_hdr_id
236: and TRANSFERRED_TO_OE_FLAG = 'I';
237: ELSE
286: l_ir_id,
287: PLA.SITE_USE_ID,
288: decode(l_op_unit_id, -1, NULL, l_op_unit_id) ,
289: 'ARRIVAL' --Bug 7662103:
290: FROM PO_REQUISITION_HEADERS RH,
291: PO_REQUISITION_LINES RL,
292: PO_LOCATION_ASSOCIATIONS_ALL PLA
293: WHERE RH.REQUISITION_HEADER_ID = RL.REQUISITION_HEADER_ID
294: AND RL.DELIVER_TO_LOCATION_ID = PLA.LOCATION_ID
604: l_source_secondary_quantity, --
605: decode(si.grade_control_flag,'Y',RL.preferred_grade,NULL) --
606: , decode(RL.URGENT_FLAG,'Y', fnd_profile.value('POR_URGENT_FLAG_SHIPMENT_PRIORITY_CODE'),null)
607: FROM PO_REQUISITION_LINES RL,
608: PO_REQUISITION_HEADERS RH,
609: PO_REQ_DISTRIBUTIONS RD, --only one distribution allowed!
610: MTL_SYSTEM_ITEMS SI,
611: PO_LOCATION_ASSOCIATIONS_ALL LA,
612: MTL_UNITS_OF_MEASURE MUM
629: Fnd_File.Put_Line(FND_FILE.LOG, '-----');
630:
631: /* Update transferred_to_oe_flag for all rows processed */
632:
633: UPDATE PO_REQUISITION_HEADERS
634: SET TRANSFERRED_TO_OE_FLAG =
635: DECODE(TRANSFERRED_TO_OE_FLAG,'I','Y','E','N')
636: WHERE TRANSFERRED_TO_OE_FLAG IN ('I', 'E');
637: