[Home] [Help]
2680: WRO is always in the primary unit of measure */
2681: ) quantity_ordered
2682: /* Sum is needed because there could be multiple POs/Reqs
2683: for the same non-stockable item */
2684: FROM cst_eam_direct_items_temp cedi,
2685: mtl_system_items_b msi
2686: WHERE cedi.item_id = msi.inventory_item_id
2687: AND cedi.organization_id = msi.organization_id
2688: AND cedi.work_order_number = p_wip_entity_id
2752: ) * NVL(wedi.unit_price, 0) * NVL(wediv.currency_rate,1)),
2753: l_ext_precision
2754: ) wedi_value
2755: FROM wip_eam_direct_items wedi,
2756: cst_eam_direct_items_temp wediv
2757: WHERE wedi.wip_entity_id = p_wip_entity_id
2758: AND wediv.work_order_number(+) = wedi.wip_entity_id
2759: AND wediv.organization_id(+) = wedi.organization_id
2760: AND wediv.direct_item_sequence_id(+) = wedi.direct_item_sequence_id
2811: FROM po_distributions_all pda,
2812: po_line_locations_all plla,
2813: po_headers_all pha,
2814: po_lines_all pla,
2815: cst_eam_direct_items_temp wediv
2816: WHERE wediv.work_order_number = p_wip_entity_id
2817: AND wediv.organization_id = l_organization_id
2818: AND wediv.task_number = pda.wip_operation_seq_num
2819: AND wediv.category_id = pla.category_id
2846: null, /* Bug 5201970 */
2847: null /* Bug 5201970 */
2848: FROM po_requisition_lines_all prla,
2849: po_requisition_headers_all prha,
2850: cst_eam_direct_items_temp wediv
2851: WHERE wediv.work_order_number = p_wip_entity_id
2852: AND wediv.organization_id = l_organization_id
2853: AND wediv.task_number = prla.wip_operation_seq_num
2854: AND wediv.category_id = prla.category_id
4292: WRO is always in the primary unit of measure */
4293: ) quantity_ordered
4294: /* Sum is needed because there could be multiple POs/Reqs
4295: for the same non-stockable item */
4296: FROM cst_eam_direct_items_temp cedi,
4297: mtl_system_items_b msi
4298: WHERE cedi.item_id = msi.inventory_item_id
4299: AND cedi.organization_id = msi.organization_id
4300: AND cedi.work_order_number = p_wip_entity_id
4575: p_prog_app_id,
4576: p_prog_id,
4577: SYSDATE
4578: FROM wip_eam_direct_items wedi,
4579: cst_eam_direct_items_temp cedi
4580: WHERE wedi.wip_entity_id = p_wip_entity_id
4581: AND cedi.work_order_number(+) = wedi.wip_entity_id
4582: AND cedi.direct_item_sequence_id(+) = wedi.direct_item_sequence_id
4583: AND cedi.organization_id(+) = wedi.organization_id
4909: FROM po_distributions_all pda,
4910: po_line_locations_all plla,
4911: po_headers_all pha,
4912: po_lines_all pla,
4913: cst_eam_direct_items_temp cedi
4914: WHERE cedi.work_order_number = p_wip_entity_id
4915: AND cedi.organization_id = l_organization_id
4916: AND cedi.task_number = pda.wip_operation_seq_num
4917: AND cedi.category_id = pla.category_id
4949: prla.item_id item_id,
4950: cedi.req_authorization_status req_auth_status
4951: FROM po_requisition_lines_all prla,
4952: po_requisition_headers_all prha,
4953: cst_eam_direct_items_temp cedi
4954: WHERE cedi.work_order_number = p_wip_entity_id
4955: AND cedi.organization_id = l_organization_id
4956: AND cedi.task_number = prla.wip_operation_seq_num
4957: AND cedi.category_id = prla.category_id
9821: set_of_books_id,
9822: order_type_lookup_code,
9823: amount,
9824: amount_delivered
9825: from cst_eam_direct_items_temp
9826: where purchasing_ou_id <> receiving_ou_id
9827: FOR UPDATE;
9828:
9829: BEGIN
9857: l_stmt_num := 10;
9858:
9859: /* Insert rows for POs for Direct Items */
9860: FORALL l_index in p_entity_id_tab.FIRST..p_entity_id_tab.LAST
9861: INSERT INTO cst_eam_direct_items_temp
9862: SELECT
9863: pd.wip_entity_id,
9864: pd.wip_operation_seq_num,
9865: pd.destination_organization_id,
10003: poh.type_lookup_code; -- DOCUMENT_TYPE
10004:
10005: l_stmt_num := 20;
10006:
10007: UPDATE cst_eam_direct_items_temp cedi
10008: SET TXN_FLOW_HEADER_ID =(SELECT transaction_flow_header_id
10009: FROM po_line_locations_all poll
10010: WHERE poll.line_location_id = cedi.line_location_id)
10011: WHERE cedi.line_location_id is not null;
10012:
10013:
10014: /* Will insert for Reqs after updation to avoid extra rows that need not participate in updation */
10015:
10016: UPDATE cst_eam_direct_items_temp cedi
10017: SET (REQUISITION_NUMBER,
10018: REQ_AUTHORIZATION_STATUS,
10019: REQUISITION_HEADER_ID,
10020: REQUISITION_LINE_ID,
10036: l_stmt_num := 30;
10037:
10038: /* Insert rows for Reqs for Direct Items */
10039: FORALL l_index in p_entity_id_tab.FIRST..p_entity_id_tab.LAST
10040: INSERT INTO cst_eam_direct_items_temp
10041: SELECT
10042: rql.wip_entity_id,
10043: rql.wip_operation_seq_num,
10044: rql.destination_organization_id,
10169: else
10170: l_unit_price := c_cedi_rec.unit_price * l_currency_rate;
10171: end if;
10172:
10173: update cst_eam_direct_items_temp
10174: set unit_price = l_unit_price,
10175: currency_rate = l_currency_rate,
10176: currency_code = l_currency_code,
10177: currency_date = l_currency_date,
10184: End If;
10185:
10186: end loop;
10187:
10188: /* Gather stats for the temporary table CST_EAM_DIRECT_ITEMS_TEMP if number
10189: of work orders are greater than 1000. Full table scan seems better in case of less
10190: work orders*/
10191: If p_entity_id_tab.COUNT> 1000 then
10192:
10213:
10214: IF l_schema IS NOT NULL
10215: THEN
10216: l_stmt_num := 29;
10217: FND_STATS.GATHER_TABLE_STATS(l_schema, 'CST_EAM_DIRECT_ITEMS_TEMP');
10218: END IF;
10219:
10220: END IF;
10221: