[Home] [Help]
290: OPI_SOURCE,
291: decode(p_lines.source_type_code, 'EXTERNAL', 2, 1 ),
292: decode(p_lines.order_source_id,10,1,0)
293: FROM opi_dbi_conc_prog_run_log log,
294: mtl_material_transactions mmt,
295: mtl_transaction_accounts mta,
296: oe_order_lines_all lines, -- child line
297: oe_order_lines_all p_lines, -- parent line
298: oe_order_headers_all header,
316: AND item.inventory_item_id = p_lines.inventory_item_id
317: AND item.organization_id = p_lines.ship_from_org_id
318: UNION ALL
319: -- 12 RMA
320: SELECT /*+ index(mmt,MTL_MATERIAL_TRANSACTIONS_N8)
321: use_nl(mmt,item,mta,h,cust_acct)
322: parallel(log) parallel(mmt) parallel(mta) parallel(l) parallel(pl)
323: parallel(cl) parallel(h) parallel(cust_acct) parallel(item)*/
324: mmt.inventory_item_id,
335: OPI_SOURCE,
336: 1,
337: 0
338: FROM opi_dbi_conc_prog_run_log log,
339: mtl_material_transactions mmt,
340: mtl_transaction_accounts mta,
341: oe_order_lines_all lines, -- child line
342: oe_order_lines_all l_lines, -- linking line
343: oe_order_lines_all p_lines, -- parent line
362: AND item.inventory_item_id = p_lines.inventory_item_id
363: AND item.organization_id = p_lines.ship_from_org_id
364: UNION ALL
365: -- Drop ship
366: SELECT /*+ leading(log) use_nl(mmt) index(mmt,MTL_MATERIAL_TRANSACTIONS_N9)
367: parallel(log) parallel(mmt) parallel(mta) parallel(l) parallel(pl)
368: parallel(h) parallel(cust_acct) parallel(item)*/
369: mmt.inventory_item_id,
370: mmt.organization_id,
380: OPI_SOURCE,
381: decode(p_mmt.transaction_type_id, 33, 1, 2),
382: 0
383: FROM opi_dbi_conc_prog_run_log log,
384: mtl_material_transactions mmt,
385: mtl_material_transactions p_mmt,
386: mtl_transaction_accounts mta,
387: oe_order_lines_all lines,
388: oe_order_lines_all p_lines, -- parent line
381: decode(p_mmt.transaction_type_id, 33, 1, 2),
382: 0
383: FROM opi_dbi_conc_prog_run_log log,
384: mtl_material_transactions mmt,
385: mtl_material_transactions p_mmt,
386: mtl_transaction_accounts mta,
387: oe_order_lines_all lines,
388: oe_order_lines_all p_lines, -- parent line
389: oe_order_headers_all header,
467: m.turns_cogs_flag,
468: m.internal_flag
469: )
470: -- 2 Regular sales order -- 8 Internal Sales Order
471: SELECT /*+ leading(log) index(mmt,MTL_MATERIAL_TRANSACTIONS_U2) use_nl(mmt,item,mta,h,cust_acct) */
472: mmt.inventory_item_id,
473: mmt.organization_id,
474: lines.line_id,
475: p_lines.line_id top_model_line_id,
483: OPI_SOURCE,
484: decode(p_lines.source_type_code, 'EXTERNAL', 2, 1 ),
485: decode(p_lines.order_source_id,10,1,0)
486: FROM opi_dbi_conc_prog_run_log log,
487: mtl_material_transactions mmt,
488: mtl_transaction_accounts mta,
489: oe_order_lines_all lines, -- child line
490: oe_order_lines_all p_lines, -- parent line
491: oe_order_headers_all header,
524: OPI_SOURCE,
525: 1,
526: 0
527: FROM opi_dbi_conc_prog_run_log log,
528: mtl_material_transactions mmt,
529: mtl_transaction_accounts mta,
530: oe_order_lines_all lines, -- child line
531: oe_order_lines_all l_lines, -- linking line
532: oe_order_lines_all p_lines, -- parent line
550: AND header.sold_to_org_id = cust_acct.cust_account_id(+)
551: AND item.inventory_item_id = p_lines.inventory_item_id
552: AND item.organization_id = p_lines.ship_from_org_id
553: UNION ALL -- drop ship
554: SELECT /*+ leading(log) use_nl(mmt) index(mmt,MTL_MATERIAL_TRANSACTIONS_N9) */
555: mmt.inventory_item_id,
556: mmt.organization_id,
557: lines.line_id,
558: p_lines.line_id top_model_line_id,
566: OPI_SOURCE,
567: decode(p_mmt.transaction_type_id, 33, 1, 2),
568: 0
569: FROM opi_dbi_conc_prog_run_log log,
570: mtl_material_transactions mmt,
571: mtl_material_transactions p_mmt,
572: mtl_transaction_accounts mta,
573: oe_order_lines_all lines,
574: oe_order_lines_all p_lines, -- parent line
567: decode(p_mmt.transaction_type_id, 33, 1, 2),
568: 0
569: FROM opi_dbi_conc_prog_run_log log,
570: mtl_material_transactions mmt,
571: mtl_material_transactions p_mmt,
572: mtl_transaction_accounts mta,
573: oe_order_lines_all lines,
574: oe_order_lines_all p_lines, -- parent line
575: oe_order_headers_all header,
785: oe_order_lines_all p_lines, -- parent line
786: oe_order_headers_all header,
787: hz_cust_accounts cust_acct,
788: mtl_system_items_b item,
789: mtl_material_transactions mmt,
790: mtl_material_transactions p_mmt,
791: (
792: SELECT gtv.transaction_id,
793: gtv.inventory_item_id,
786: oe_order_headers_all header,
787: hz_cust_accounts cust_acct,
788: mtl_system_items_b item,
789: mtl_material_transactions mmt,
790: mtl_material_transactions p_mmt,
791: (
792: SELECT gtv.transaction_id,
793: gtv.inventory_item_id,
794: gtv.organization_id,
1141: AND item.inventory_item_id = p_lines.inventory_item_id
1142: AND item.organization_id = p_lines.ship_from_org_id
1143: UNION ALL
1144: -- Drop Ship
1145: SELECT /*+ ordered use_nl(mmt, p_mmt, lines, p_lines, header, cust_acct, item) index(mmt,mtl_material_transactions_u1)*/
1146: gtv.inventory_item_id,
1147: gtv.organization_id,
1148: lines.line_id,
1149: p_lines.line_id top_model_line_id,
1208: gtv.inventory_item_id,
1209: gtv.organization_id,
1210: gtv.line_id,
1211: gtv.transaction_date) gtv,
1212: mtl_material_transactions mmt,
1213: mtl_material_transactions p_mmt,
1214: oe_order_lines_all lines, -- child line
1215: oe_order_lines_all p_lines, -- parent line
1216: oe_order_headers_all header,
1209: gtv.organization_id,
1210: gtv.line_id,
1211: gtv.transaction_date) gtv,
1212: mtl_material_transactions mmt,
1213: mtl_material_transactions p_mmt,
1214: oe_order_lines_all lines, -- child line
1215: oe_order_lines_all p_lines, -- parent line
1216: oe_order_headers_all header,
1217: hz_cust_accounts cust_acct,