[Home] [Help]
2902: WRO is always in the primary unit of measure */
2903: ) quantity_ordered
2904: /* Sum is needed because there could be multiple POs/Reqs
2905: for the same non-stockable item */
2906: FROM cst_eam_direct_items_temp cedi,
2907: mtl_system_items_b msi
2908: WHERE cedi.item_id = msi.inventory_item_id
2909: AND cedi.organization_id = msi.organization_id
2910: AND cedi.work_order_number = p_wip_entity_id
2974: ) * NVL(wedi.unit_price, 0) * NVL(wediv.currency_rate,1)),
2975: l_ext_precision
2976: ) wedi_value
2977: FROM wip_eam_direct_items wedi,
2978: cst_eam_direct_items_temp wediv
2979: WHERE wedi.wip_entity_id = p_wip_entity_id
2980: AND wediv.work_order_number(+) = wedi.wip_entity_id
2981: AND wediv.organization_id(+) = wedi.organization_id
2982: AND wediv.direct_item_sequence_id(+) = wedi.direct_item_sequence_id
3033: FROM po_distributions_all pda,
3034: po_line_locations_all plla,
3035: po_headers_all pha,
3036: po_lines_all pla,
3037: cst_eam_direct_items_temp wediv
3038: WHERE wediv.work_order_number = p_wip_entity_id
3039: AND wediv.organization_id = l_organization_id
3040: AND wediv.task_number = pda.wip_operation_seq_num
3041: AND wediv.category_id = pla.category_id
3068: null, /* Bug 5201970 */
3069: null /* Bug 5201970 */
3070: FROM po_requisition_lines_all prla,
3071: po_requisition_headers_all prha,
3072: cst_eam_direct_items_temp wediv
3073: WHERE wediv.work_order_number = p_wip_entity_id
3074: AND wediv.organization_id = l_organization_id
3075: AND wediv.task_number = prla.wip_operation_seq_num
3076: AND wediv.category_id = prla.category_id
4514: WRO is always in the primary unit of measure */
4515: ) quantity_ordered
4516: /* Sum is needed because there could be multiple POs/Reqs
4517: for the same non-stockable item */
4518: FROM cst_eam_direct_items_temp cedi,
4519: mtl_system_items_b msi
4520: WHERE cedi.item_id = msi.inventory_item_id
4521: AND cedi.organization_id = msi.organization_id
4522: AND cedi.work_order_number = p_wip_entity_id
4797: p_prog_app_id,
4798: p_prog_id,
4799: SYSDATE
4800: FROM wip_eam_direct_items wedi,
4801: cst_eam_direct_items_temp cedi
4802: WHERE wedi.wip_entity_id = p_wip_entity_id
4803: AND cedi.work_order_number(+) = wedi.wip_entity_id
4804: AND cedi.direct_item_sequence_id(+) = wedi.direct_item_sequence_id
4805: AND cedi.organization_id(+) = wedi.organization_id
5131: FROM po_distributions_all pda,
5132: po_line_locations_all plla,
5133: po_headers_all pha,
5134: po_lines_all pla,
5135: cst_eam_direct_items_temp cedi
5136: WHERE cedi.work_order_number = p_wip_entity_id
5137: AND cedi.organization_id = l_organization_id
5138: AND cedi.task_number = pda.wip_operation_seq_num
5139: AND cedi.category_id = pla.category_id
5171: prla.item_id item_id,
5172: cedi.req_authorization_status req_auth_status
5173: FROM po_requisition_lines_all prla,
5174: po_requisition_headers_all prha,
5175: cst_eam_direct_items_temp cedi
5176: WHERE cedi.work_order_number = p_wip_entity_id
5177: AND cedi.organization_id = l_organization_id
5178: AND cedi.task_number = prla.wip_operation_seq_num
5179: AND cedi.category_id = prla.category_id
10534: set_of_books_id,
10535: order_type_lookup_code,
10536: amount,
10537: amount_delivered
10538: from cst_eam_direct_items_temp
10539: where purchasing_ou_id <> receiving_ou_id
10540: FOR UPDATE;
10541:
10542: BEGIN
10570: l_stmt_num := 10;
10571:
10572: /* Insert rows for POs for Direct Items */
10573: FORALL l_index in p_entity_id_tab.FIRST..p_entity_id_tab.LAST
10574: INSERT INTO cst_eam_direct_items_temp
10575: SELECT
10576: pd.wip_entity_id,
10577: pd.wip_operation_seq_num,
10578: pd.destination_organization_id,
10716: poh.type_lookup_code; -- DOCUMENT_TYPE
10717:
10718: l_stmt_num := 20;
10719:
10720: UPDATE cst_eam_direct_items_temp cedi
10721: SET TXN_FLOW_HEADER_ID =(SELECT transaction_flow_header_id
10722: FROM po_line_locations_all poll
10723: WHERE poll.line_location_id = cedi.line_location_id)
10724: WHERE cedi.line_location_id is not null;
10725:
10726:
10727: /* Will insert for Reqs after updation to avoid extra rows that need not participate in updation */
10728:
10729: UPDATE cst_eam_direct_items_temp cedi
10730: SET (REQUISITION_NUMBER,
10731: REQ_AUTHORIZATION_STATUS,
10732: REQUISITION_HEADER_ID,
10733: REQUISITION_LINE_ID,
10749: l_stmt_num := 30;
10750:
10751: /* Insert rows for Reqs for Direct Items */
10752: FORALL l_index in p_entity_id_tab.FIRST..p_entity_id_tab.LAST
10753: INSERT INTO cst_eam_direct_items_temp
10754: SELECT
10755: rql.wip_entity_id,
10756: rql.wip_operation_seq_num,
10757: rql.destination_organization_id,
10882: else
10883: l_unit_price := c_cedi_rec.unit_price * l_currency_rate;
10884: end if;
10885:
10886: update cst_eam_direct_items_temp
10887: set unit_price = l_unit_price,
10888: currency_rate = l_currency_rate,
10889: currency_code = l_currency_code,
10890: currency_date = l_currency_date,
10897: End If;
10898:
10899: end loop;
10900:
10901: /* Gather stats for the temporary table CST_EAM_DIRECT_ITEMS_TEMP if number
10902: of work orders are greater than 1000. Full table scan seems better in case of less
10903: work orders*/
10904: If p_entity_id_tab.COUNT> 1000 then
10905:
10926:
10927: IF l_schema IS NOT NULL
10928: THEN
10929: l_stmt_num := 29;
10930: FND_STATS.GATHER_TABLE_STATS(l_schema, 'CST_EAM_DIRECT_ITEMS_TEMP');
10931: END IF;
10932:
10933: END IF;
10934: