APPS.PO_CREATE_ISO dependencies on PO_REQUISITION_HEADERS

Line 43: Step 4: insert into oe_headers_iface_all from po_requisition_headers

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.

Line 132: select /*+ leading(HDR) index(HDR PO_REQUISITION_HEADERS_N4) USE_NL(HDR

128: /* bug 16544597 : reverting the changes done as part of 14175712 as they are
129: causing functional regression also modifying query to improve performance*/
130:
131: CURSOR REQ_LINES IS
132: select /*+ leading(HDR) index(HDR PO_REQUISITION_HEADERS_N4) USE_NL(HDR
133: LIN PLA)*/
134: nvl(org.operating_unit,-1),
135: hdr.requisition_header_id,
136: lin.requisition_line_id,

Line 143: po_requisition_headers hdr,

139: where pla.location_id = lin.deliver_to_location_id
140: AND nvl(PLA.ORG_ID,-1) = nvl(org.operating_unit,-1)
141: AND rownum=1),-1) customer_id --bug 8692047
142: from po_requisition_lines lin,
143: po_requisition_headers hdr,
144: org_organization_definitions org
145: where lin.requisition_header_id = hdr.requisition_header_id
146: and lin.source_organization_id = org.organization_id
147: and hdr.transferred_to_oe_flag = 'I'

Line 176: /*UPDATE PO_REQUISITION_HEADERS PRH

172:
173: /* Bug 2630523 Performance of the following SQL will be good if it choose
174: Hash Join. So added the HASH_SJ hint to improve the performance */
175:
176: /*UPDATE PO_REQUISITION_HEADERS PRH
177: SET PRH.TRANSFERRED_TO_OE_FLAG = 'I'
178: WHERE nvl(PRH.TRANSFERRED_TO_OE_FLAG,'N') = 'N'
179: AND PRH.AUTHORIZATION_STATUS = 'APPROVED'
180: AND exists (select /*+ HASH_SJ */ /*'At least one inventory sourced line'

Line 191: UPDATE po_requisition_headers prh

187:
188: /* Bug 7699084 Commented the above sql and introduced the below sql for performance gains
189: Added an Nvl around the transferred_to_oe_flag check*/
190:
191: UPDATE po_requisition_headers prh
192: SET prh.transferred_to_oe_flag = 'I'
193: WHERE prh.authorization_status = 'APPROVED'
194: AND prh.requisition_header_id IN (SELECT /*+ index ( PRL po_requisition_lines_f5 ) */
195: DISTINCT prl.requisition_header_id

Line 207: UPDATE PO_REQUISITION_HEADERS PRH

203: Fnd_File.Put_Line(FND_FILE.LOG,to_char(SQL%ROWCOUNT)||' Reqs selected for processing');
204:
205: ELSE
206:
207: UPDATE PO_REQUISITION_HEADERS PRH
208: SET PRH.TRANSFERRED_TO_OE_FLAG = 'I'
209: WHERE /*nvl(PRH.TRANSFERRED_TO_OE_FLAG,'N') = 'N' AND Bug 7699084*/
210: PRH.AUTHORIZATION_STATUS = 'APPROVED'
211: AND PRH.REQUISITION_HEADER_ID = p_req_header_id

Line 290: UPDATE PO_REQUISITION_HEADERS

286: l_ac_id,
287: l_ir_id);
288:
289: IF (l_error_flag = 'Y') then
290: UPDATE PO_REQUISITION_HEADERS
291: SET TRANSFERRED_TO_OE_FLAG = 'E'
292: WHERE REQUISITION_HEADER_ID = l_req_hdr_id
293: and TRANSFERRED_TO_OE_FLAG = 'I';
294: ELSE

Line 354: FROM PO_REQUISITION_HEADERS RH,

350: l_ir_id,
351: PLA.SITE_USE_ID,
352: decode(l_op_unit_id, -1, NULL, l_op_unit_id) ,
353: 'ARRIVAL' --Bug 7662103:
354: FROM PO_REQUISITION_HEADERS RH,
355: PO_REQUISITION_LINES RL,
356: PO_LOCATION_ASSOCIATIONS_ALL PLA
357: WHERE RH.REQUISITION_HEADER_ID = RL.REQUISITION_HEADER_ID
358: AND RL.DELIVER_TO_LOCATION_ID = PLA.LOCATION_ID

Line 740: PO_REQUISITION_HEADERS RH,

736: round( l_source_secondary_quantity,9), --RL.SECONDARY_QUANTITY,
737: decode(si.grade_control_flag,'Y',RL.preferred_grade,NULL) -- RL.PREFERRED_GRADE
738: , decode(RL.URGENT_FLAG,'Y', fnd_profile.value('POR_URGENT_FLAG_SHIPMENT_PRIORITY_CODE'),null)
739: FROM PO_REQUISITION_LINES RL,
740: PO_REQUISITION_HEADERS RH,
741: PO_REQ_DISTRIBUTIONS RD, --only one distribution allowed!
742: MTL_SYSTEM_ITEMS SI,
743: PO_LOCATION_ASSOCIATIONS_ALL LA,
744: MTL_UNITS_OF_MEASURE MUM

Line 767: UPDATE PO_REQUISITION_HEADERS

763: Fnd_File.Put_Line(FND_FILE.LOG, '-----');
764:
765: /* Update transferred_to_oe_flag for all rows processed */
766:
767: UPDATE PO_REQUISITION_HEADERS
768: SET TRANSFERRED_TO_OE_FLAG =
769: DECODE(TRANSFERRED_TO_OE_FLAG,'I','Y','E','N')
770: WHERE TRANSFERRED_TO_OE_FLAG IN ('I', 'E');
771:

Line 779: FROM po_requisition_headers prh

775: LOOP
776: UPDATE po_requisition_lines prl
777: SET prl.transferred_to_oe_flag =
778: (SELECT prh.transferred_to_oe_flag
779: FROM po_requisition_headers prh
780: WHERE prh.requisition_header_id = l_req_hdr_id_tbl(i)
781: )
782: WHERE prl.requisition_header_id = l_req_hdr_id_tbl(i)
783: and prl.source_type_code = 'INVENTORY'