[Home] [Help]
197:
198: CURSOR mscSupply(p_refresh_number NUMBER,
199: p_sr_instance_id NUMBER,
200: p_language_code VARCHAR2) IS
201: select --msc_sup_dem_entries_s.nextval transaction_id
202: G_SR_INSTANCE_ID sr_instance_id
203: ,G_PLAN_ID plan_id
204: ,decode(ms.order_type, 11, mcr.object_id , G_OEM_ID) publisher_id -- Bug 4395985
205: ,decode(ms.order_type, 11, mtpm1.company_key , mcsil.company_site_id) publisher_site_id
1552: p_sr_instance_id NUMBER,
1553: p_language_code VARCHAR2) IS
1554:
1555: select
1556: -- msc_sup_dem_entries_s.nextval TRANSACTION_ID,
1557: G_PLAN_ID PLAN_ID,
1558: G_SR_INSTANCE_ID SR_INSTANCE_ID,
1559: G_OEM_ID PUBLISHER_ID,
1560: mcs.company_site_id PUBLISHER_SITE_ID,
1703: and nvl(mso.reservation_type, -99) = 1
1704: and mso.demand_source_type <> 8 /* Ignore Internal Sales orders */
1705: UNION /* sales order in supplier modeled orgs (multi company plng) */
1706: select
1707: -- msc_sup_dem_entries_s.nextval TRANSACTION_ID,
1708: G_PLAN_ID PLAN_ID,
1709: G_SR_INSTANCE_ID SR_INSTANCE_ID,
1710: G_OEM_ID PUBLISHER_ID,
1711: mcs_org.company_site_id PUBLISHER_SITE_ID,
2172: a_supplier_update number_arr := number_arr();
2173: a_customer_update number_arr := number_arr();
2174: a_resultant_update number_arr := number_arr();
2175:
2176: l_owner_item_name MSC_SUP_DEM_ENTRIES.OWNER_ITEM_NAME%TYPE;
2177: l_customer_item_name MSC_SUP_DEM_ENTRIES.CUSTOMER_ITEM_NAME%TYPE;
2178: l_supplier_item_name MSC_SUP_DEM_ENTRIES.SUPPLIER_ITEM_NAME%TYPE;
2179: l_owner_item_desc MSC_SUP_DEM_ENTRIES.OWNER_ITEM_DESCRIPTION%TYPE;
2180: l_customer_item_desc MSC_SUP_DEM_ENTRIES.CUSTOMER_ITEM_DESCRIPTION%TYPE;
2173: a_customer_update number_arr := number_arr();
2174: a_resultant_update number_arr := number_arr();
2175:
2176: l_owner_item_name MSC_SUP_DEM_ENTRIES.OWNER_ITEM_NAME%TYPE;
2177: l_customer_item_name MSC_SUP_DEM_ENTRIES.CUSTOMER_ITEM_NAME%TYPE;
2178: l_supplier_item_name MSC_SUP_DEM_ENTRIES.SUPPLIER_ITEM_NAME%TYPE;
2179: l_owner_item_desc MSC_SUP_DEM_ENTRIES.OWNER_ITEM_DESCRIPTION%TYPE;
2180: l_customer_item_desc MSC_SUP_DEM_ENTRIES.CUSTOMER_ITEM_DESCRIPTION%TYPE;
2181: l_supplier_item_desc MSC_SUP_DEM_ENTRIES.SUPPLIER_ITEM_DESCRIPTION%TYPE;
2174: a_resultant_update number_arr := number_arr();
2175:
2176: l_owner_item_name MSC_SUP_DEM_ENTRIES.OWNER_ITEM_NAME%TYPE;
2177: l_customer_item_name MSC_SUP_DEM_ENTRIES.CUSTOMER_ITEM_NAME%TYPE;
2178: l_supplier_item_name MSC_SUP_DEM_ENTRIES.SUPPLIER_ITEM_NAME%TYPE;
2179: l_owner_item_desc MSC_SUP_DEM_ENTRIES.OWNER_ITEM_DESCRIPTION%TYPE;
2180: l_customer_item_desc MSC_SUP_DEM_ENTRIES.CUSTOMER_ITEM_DESCRIPTION%TYPE;
2181: l_supplier_item_desc MSC_SUP_DEM_ENTRIES.SUPPLIER_ITEM_DESCRIPTION%TYPE;
2182: l_lead_time NUMBER;
2175:
2176: l_owner_item_name MSC_SUP_DEM_ENTRIES.OWNER_ITEM_NAME%TYPE;
2177: l_customer_item_name MSC_SUP_DEM_ENTRIES.CUSTOMER_ITEM_NAME%TYPE;
2178: l_supplier_item_name MSC_SUP_DEM_ENTRIES.SUPPLIER_ITEM_NAME%TYPE;
2179: l_owner_item_desc MSC_SUP_DEM_ENTRIES.OWNER_ITEM_DESCRIPTION%TYPE;
2180: l_customer_item_desc MSC_SUP_DEM_ENTRIES.CUSTOMER_ITEM_DESCRIPTION%TYPE;
2181: l_supplier_item_desc MSC_SUP_DEM_ENTRIES.SUPPLIER_ITEM_DESCRIPTION%TYPE;
2182: l_lead_time NUMBER;
2183: l_tp_customer_id NUMBER;
2176: l_owner_item_name MSC_SUP_DEM_ENTRIES.OWNER_ITEM_NAME%TYPE;
2177: l_customer_item_name MSC_SUP_DEM_ENTRIES.CUSTOMER_ITEM_NAME%TYPE;
2178: l_supplier_item_name MSC_SUP_DEM_ENTRIES.SUPPLIER_ITEM_NAME%TYPE;
2179: l_owner_item_desc MSC_SUP_DEM_ENTRIES.OWNER_ITEM_DESCRIPTION%TYPE;
2180: l_customer_item_desc MSC_SUP_DEM_ENTRIES.CUSTOMER_ITEM_DESCRIPTION%TYPE;
2181: l_supplier_item_desc MSC_SUP_DEM_ENTRIES.SUPPLIER_ITEM_DESCRIPTION%TYPE;
2182: l_lead_time NUMBER;
2183: l_tp_customer_id NUMBER;
2184: l_tp_customer_site_id NUMBER;
2177: l_customer_item_name MSC_SUP_DEM_ENTRIES.CUSTOMER_ITEM_NAME%TYPE;
2178: l_supplier_item_name MSC_SUP_DEM_ENTRIES.SUPPLIER_ITEM_NAME%TYPE;
2179: l_owner_item_desc MSC_SUP_DEM_ENTRIES.OWNER_ITEM_DESCRIPTION%TYPE;
2180: l_customer_item_desc MSC_SUP_DEM_ENTRIES.CUSTOMER_ITEM_DESCRIPTION%TYPE;
2181: l_supplier_item_desc MSC_SUP_DEM_ENTRIES.SUPPLIER_ITEM_DESCRIPTION%TYPE;
2182: l_lead_time NUMBER;
2183: l_tp_customer_id NUMBER;
2184: l_tp_customer_site_id NUMBER;
2185: l_location_id NUMBER;
2185: l_location_id NUMBER;
2186: l_org_location_id NUMBER;
2187: l_session_id NUMBER;
2188: l_regions_return_status VARCHAR2(1);
2189: l_tp_uom MSC_SUP_DEM_ENTRIES.TP_UOM_CODE%TYPE;
2190: l_conversion_found BOOLEAN;
2191: l_conversion_rate NUMBER;
2192:
2193: l_process_lead_time BOOLEAN;
2406: v_suprep_refresh_number := v_refresh_number;
2407: END IF;
2408:
2409: -- ===============================================================================
2410: -- If complete refresh then delete following records in msc_sup_dem_entries
2411: --
2412: -- 1. All PO Order Types (13,20,16,15) which belong to Publisher = "My Company"
2413: -- and publisher_site IN (All enaled inventory organizations for ERP instance)
2414: -- 2. All Allocated OH records (order type = G_ALLOC_ONHAND) which belong to
2450: IF (v_so_lrtype = 'C') then
2451: /* bug:3584822 -- Delete S.O only if the Sales orders
2452: is YES in complete refresh */
2453: v_sql_stmt:=
2454: ' delete msc_sup_dem_entries msde'
2455: ||' where msde.publisher_id = 1'
2456: ||' and msde.publisher_site_id '||v_in_org_str
2457: ||' and msde.plan_id = -1 '
2458: ||' and msde.publisher_order_type IN '
2459: ||'( 9, 10, 13, 20, 14, 15, 16 '
2460: ||') ';
2461: ELSE
2462: v_sql_stmt:=
2463: ' delete msc_sup_dem_entries msde'
2464: ||' where msde.publisher_id = 1'
2465: ||' and msde.publisher_site_id '||v_in_org_str
2466: ||' and msde.plan_id = -1 '
2467: ||' and msde.publisher_order_type IN '
2478:
2479: IF ( v_lrtype = 'P' AND p_po_enabled_flag = MSC_CL_COLLECTION.SYS_YES) THEN
2480:
2481: v_sql_stmt:=
2482: ' delete msc_sup_dem_entries msde'
2483: ||' where msde.publisher_id = 1'
2484: ||' and msde.publisher_site_id '||v_in_org_str
2485: ||' and msde.plan_id = -1 '
2486: ||' and msde.publisher_order_type IN '
2496:
2497: IF ( v_lrtype = 'P' AND p_oh_enabled_flag = MSC_CL_COLLECTION.SYS_YES) THEN
2498:
2499: v_sql_stmt:=
2500: ' delete msc_sup_dem_entries msde'
2501: ||' where msde.publisher_id = 1'
2502: ||' and msde.publisher_site_id '||v_in_org_str
2503: ||' and msde.plan_id = -1 '
2504: ||' and msde.publisher_order_type IN '
2514:
2515: IF ( v_lrtype = 'P' AND p_so_enabled_flag = MSC_CL_COLLECTION.SYS_YES) THEN
2516:
2517: v_sql_stmt:=
2518: ' delete msc_sup_dem_entries msde'
2519: ||' where msde.publisher_id = 1'
2520: ||' and msde.publisher_site_id '||v_in_org_str
2521: ||' and msde.plan_id = -1 '
2522: ||' and msde.publisher_order_type IN '
2578:
2579: v_sql_stmt := NULL;
2580:
2581: v_sql_stmt:=
2582: ' delete msc_sup_dem_entries msde'
2583: ||' where msde.publisher_id = 1'
2584: ||' and msde.publisher_site_id '||v_in_org_str
2585: ||' and msde.plan_id = -1 '
2586: ||' and msde.publisher_order_type '||v_in_ot_str;
2590:
2591: COMMIT;
2592:
2593: EXCEPTION WHEN OTHERS THEN
2594: LOG_MESSAGE('Error while deleting records from msc_sup_dem_entries in case of Automatic Collections');
2595: LOG_MESSAGE(SQLERRM);
2596:
2597: RETCODE := G_ERROR;
2598: RETURN;
2605: ) THEN
2606:
2607: -- =================================================================
2608: -- If PO data is collected in netchange mode then we want to delete
2609: -- following records from msc_sup_dem_entries
2610: -- Records with =>
2611: -- publisher_id = 1
2612: -- order_type = G_REQ
2613: -- order_number = null
2619:
2620: v_sql_stmt := NULL;
2621:
2622: v_sql_stmt:=
2623: ' delete msc_sup_dem_entries msde'
2624: ||' where msde.publisher_id = 1'
2625: ||' and msde.publisher_site_id '||v_in_org_str
2626: ||' and msde.plan_id = -1 '
2627: ||' and msde.publisher_order_type = 20 '
2630: EXECUTE IMMEDIATE v_sql_stmt;
2631:
2632: COMMIT;
2633: EXCEPTION WHEN OTHERS THEN
2634: LOG_MESSAGE('Error while deleting records from msc_sup_dem_entries created using MOE');
2635: LOG_MESSAGE(SQLERRM);
2636:
2637: RETCODE := G_ERROR;
2638: RETURN;
2726:
2727: LOG_MESSAGE('Total Supply records fetched :'||t_pub_id.COUNT);
2728:
2729: --===========================================================================================
2730: -- Derive dependant column values before updating or inserting records in msc_sup_dem_entries
2731: --===========================================================================================
2732: IF t_pub_id.COUNT > 0 THEN
2733:
2734: FOR j in 1.. t_pub_id.COUNT LOOP
2843: BEGIN
2844:
2845: FORALL j in 1..t_pub_id.COUNT
2846:
2847: UPDATE msc_sup_dem_entries
2848: SET last_refresh_number = msc_cl_refresh_s.nextval,
2849: quantity = round((nvl(t_quantity(j),0)),6),
2850: tp_quantity = round((nvl(t_tp_quantity(j),0)),6),
2851: comments = t_comments(j),
2886:
2887: COMMIT;
2888:
2889: EXCEPTION WHEN OTHERS THEN
2890: LOG_MESSAGE('Error while updating msc_sup_dem_entries');
2891: LOG_MESSAGE(SQLERRM);
2892: RETCODE := G_ERROR;
2893: END;
2894:
3037:
3038: IF a_ins_count.COUNT > 0 THEN
3039: BEGIN
3040: FORALL j in 1..a_ins_count.COUNT
3041: insert into msc_sup_dem_entries
3042: (
3043: sr_instance_id
3044: ,transaction_id
3045: ,plan_id
3107: ,planner_code --Bug 4424426
3108: )values
3109: (
3110: t_ins_sr_instance_id(j),
3111: msc_sup_dem_entries_s.nextval,
3112: --t_ins_transaction_id(j),
3113: t_ins_plan_id(j),
3114: t_ins_pub_id(j),
3115: t_ins_pub_site_id(j),
3177:
3178: COMMIT;
3179:
3180: EXCEPTION WHEN OTHERS THEN
3181: LOG_MESSAGE('Error while inserting records into msc_sup_dem_entries');
3182: LOG_MESSAGE(SQLERRM);
3183: ROLLBACK;
3184: RETCODE := G_ERROR;
3185: END;
3305:
3306: BEGIN
3307: FORALL i in 1..t_plan_id.COUNT
3308:
3309: update msc_sup_dem_entries
3310: set quantity = round(t_quantity(i), 6),
3311: bucket_type = t_bkt_type(i),
3312: uom_code = t_uom(i) ,
3313: primary_uom = t_pri_uom(i),
3451: IF a_ins_count.COUNT > 0 THEN
3452:
3453: BEGIN
3454: FORALL i IN 1..a_ins_count.COUNT
3455: INSERT INTO MSC_SUP_DEM_ENTRIES
3456: ( transaction_id,
3457: plan_id,
3458: sr_instance_id,
3459: publisher_id,
3503: base_item_name,
3504: planner_code--Bug 4424426
3505: )
3506: values
3507: ( msc_sup_dem_entries_s.nextval,
3508: t_ins_plan_id(i),
3509: t_ins_sr_instance_id(i),
3510: t_ins_pub_id(i),
3511: t_ins_pub_site_id(i),
3558: );
3559:
3560: COMMIT;
3561: EXCEPTION WHEN OTHERS THEN
3562: LOG_MESSAGE('ERROR while inserting from unallocOnhand to msc_sup_dem_entries ');
3563: LOG_MESSAGE(SQLERRM);
3564: ROLLBACK;
3565: RETCODE := G_ERROR;
3566: END;
3832: BEGIN
3833:
3834: FORALL i in 1..t_plan_id.COUNT
3835:
3836: update msc_sup_dem_entries
3837: set quantity = round(t_quantity(i), 6),
3838: bucket_type = t_bkt_type(i),
3839: uom_code = t_uom(i) ,
3840: primary_uom = t_pri_uom(i),
3879: ** If no performance hit, the SQL's can be merged
3880: */
3881: FORALL i in 1..t_plan_id.COUNT
3882:
3883: update msc_sup_dem_entries
3884: set quantity = round(t_quantity(i), 6),
3885: bucket_type = t_bkt_type(i),
3886: uom_code = t_uom(i) ,
3887: primary_uom = t_pri_uom(i),
4060: IF a_ins_count.COUNT > 0 THEN
4061:
4062: BEGIN
4063: FORALL i IN 1..a_ins_count.COUNT
4064: INSERT INTO MSC_SUP_DEM_ENTRIES
4065: ( transaction_id,
4066: plan_id,
4067: sr_instance_id,
4068: publisher_id,
4113: , base_item_name
4114: ,planner_code --Bug 4424426
4115: )
4116: values
4117: ( msc_sup_dem_entries_s.nextval,
4118: t_ins_plan_id(i),
4119: t_ins_sr_instance_id(i),
4120: t_ins_pub_id(i),
4121: t_ins_pub_site_id(i),
4184: );
4185:
4186: COMMIT;
4187: EXCEPTION WHEN OTHERS THEN
4188: LOG_MESSAGE('ERROR while inserting from allocOnhand to msc_sup_dem_entries ');
4189: LOG_MESSAGE(SQLERRM);
4190: ROLLBACK;
4191: RETCODE := G_ERROR;
4192: END;
4281: BEGIN
4282:
4283: FORALL i in 1..t_plan_id.COUNT
4284:
4285: update msc_sup_dem_entries
4286: set quantity = round(t_quantity(i), 6),
4287: bucket_type = t_bkt_type(i),
4288: uom_code = t_uom(i) ,
4289: primary_uom = t_pri_uom(i),
4567: -- ==================
4568: IF a_ins_count.COUNT > 0 THEN
4569: BEGIN
4570: FORALL i IN 1..a_ins_count.COUNT
4571: INSERT INTO MSC_SUP_DEM_ENTRIES
4572: (
4573: transaction_id,
4574: plan_id ,
4575: sr_instance_id ,
4635: planner_code --Bug 4424426
4636: )
4637: values
4638: (
4639: msc_sup_dem_entries_s.nextval,
4640: t_ins_plan_id(i) ,
4641: t_ins_sr_instance_id(i),
4642: t_ins_pub_id(i) ,
4643: t_ins_pub_site_id(i) ,
4702: );
4703:
4704: COMMIT;
4705: EXCEPTION WHEN OTHERS THEN
4706: LOG_MESSAGE('ERROR while inserting from Sales orders to msc_sup_dem_entries ');
4707: LOG_MESSAGE(SQLERRM);
4708: ROLLBACK;
4709: RETCODE := G_ERROR;
4710: END;
4714: BEGIN
4715:
4716: FORALL i in 1..t_plan_id.COUNT
4717:
4718: update msc_sup_dem_entries sd
4719: set link_trans_id = t_line_num(i)
4720: where sd.plan_id = G_PLAN_ID
4721: and sd.sr_instance_id = G_SR_INSTANCE_ID
4722: and sd.inventory_item_id = t_item_id(i)
4863: ' msc_trading_partners mtp ,'||
4864: ' msc_company_site_id_lid mcsil ,'||
4865: ' msc_company_sites mcs ,'||
4866: ' msc_companies mc ,'||
4867: ' msc_sup_dem_entries asn ,'||
4868: ' msc_company_relationships mcr ,'||
4869: ' msc_trading_partner_maps mtpm ,'||
4870: ' msc_companies mc1 ,'||
4871: ' msc_company_sites mcs1 ,'||
4940: ' msc_trading_partners mtp,'||
4941: ' msc_company_site_id_lid mcsil,'||
4942: ' msc_company_sites mcs,'||
4943: ' msc_companies mc,'||
4944: ' msc_sup_dem_entries asn,'||
4945: ' msc_company_relationships mcr,'||
4946: ' msc_trading_partner_maps mtpm,'||
4947: ' msc_companies mc1,'||
4948: ' msc_company_sites mcs1,'||
5019: ' msc_trading_partners mtp,'||
5020: ' msc_company_site_id_lid mcsil,'||
5021: ' msc_company_sites mcs,'||
5022: ' msc_companies mc,'||
5023: ' msc_sup_dem_entries asn,'||
5024: ' msc_company_relationships mcr,'||
5025: ' msc_trading_partner_maps mtpm,'||
5026: ' msc_companies mc1,'||
5027: ' msc_company_sites mcs1,'||
5096: ' msc_trading_partners mtp,'||
5097: ' msc_company_site_id_lid mcsil,'||
5098: ' msc_company_sites mcs,'||
5099: ' msc_companies mc,'||
5100: ' msc_sup_dem_entries asn,'||
5101: ' msc_company_relationships mcr,'||
5102: ' msc_trading_partner_maps mtpm,'||
5103: ' msc_companies mc1,'||
5104: ' msc_company_sites mcs1,'||
5174: ' msc_trading_partners mtp ,'||
5175: ' msc_company_site_id_lid mcsil ,'||
5176: ' msc_company_sites mcs ,'||
5177: ' msc_companies mc ,'||
5178: ' msc_sup_dem_entries asn ,'||
5179: ' msc_company_relationships mcr ,'||
5180: ' msc_trading_partner_maps mtpm ,'||
5181: ' msc_companies mc1 ,'||
5182: ' msc_company_sites mcs1 ,'||
5251: ' msc_trading_partners mtp,'||
5252: ' msc_company_site_id_lid mcsil,'||
5253: ' msc_company_sites mcs,'||
5254: ' msc_companies mc,'||
5255: ' msc_sup_dem_entries asn,'||
5256: ' msc_company_relationships mcr,'||
5257: ' msc_trading_partner_maps mtpm,'||
5258: ' msc_companies mc1,'||
5259: ' msc_company_sites mcs1,'||
5330: ' msc_trading_partners mtp,'||
5331: ' msc_company_site_id_lid mcsil,'||
5332: ' msc_company_sites mcs,'||
5333: ' msc_companies mc,'||
5334: ' msc_sup_dem_entries asn,'||
5335: ' msc_company_relationships mcr,'||
5336: ' msc_trading_partner_maps mtpm,'||
5337: ' msc_companies mc1,'||
5338: ' msc_company_sites mcs1,'||
5407: ' msc_trading_partners mtp,'||
5408: ' msc_company_site_id_lid mcsil,'||
5409: ' msc_company_sites mcs,'||
5410: ' msc_companies mc,'||
5411: ' msc_sup_dem_entries asn,'||
5412: ' msc_company_relationships mcr,'||
5413: ' msc_trading_partner_maps mtpm,'||
5414: ' msc_companies mc1,'||
5415: ' msc_company_sites mcs1,'||
5614: BEGIN
5615:
5616: FORALL i in 1..t_pub_id.COUNT
5617:
5618: update msc_sup_dem_entries
5619: set key_date = t_key_date(i),
5620: receipt_date = t_receipt_date(i),
5621: quantity = decode(t_status_code(i),'IT',t_quantity(i),0),
5622: primary_quantity = decode(t_status_code(i),'IT',t_quantity(i),0),
5734:
5735: IF a_ins_count.COUNT > 0 THEN
5736: BEGIN
5737: FORALL j in 1..a_ins_count.COUNT
5738: insert into msc_sup_dem_entries
5739: (
5740: sr_instance_id
5741: ,transaction_id
5742: ,plan_id
5798: ,planner_code--Bug 4424426
5799: )values
5800: (
5801: G_SR_INSTANCE_ID,
5802: msc_sup_dem_entries_s.nextval,
5803: G_PLAN_ID,
5804: t_ins_pub_id(j),
5805: t_ins_pub_site_id(j),
5806: t_ins_pub(j),
5867:
5868: COMMIT;
5869:
5870: EXCEPTION WHEN OTHERS THEN
5871: LOG_MESSAGE('Error while inserting records into msc_sup_dem_entries');
5872: LOG_MESSAGE(SQLERRM);
5873: ROLLBACK;
5874: RETCODE := G_ERROR;
5875: END;
5881: LOG_MESSAGE('updating pegging info for int reqs using ASN.');
5882:
5883: FORALL i in 1..t_pub_id.COUNT
5884:
5885: update msc_sup_dem_entries sd
5886: set link_trans_id = t_delivery_id(i)
5887: where sd.plan_id = G_PLAN_ID
5888: and sd.sr_instance_id = G_SR_INSTANCE_ID
5889: and sd.inventory_item_id = t_item_id(i)
5926: IF v_lrtype = 'C' THEN
5927:
5928: BEGIN
5929:
5930: UPDATE MSC_SUP_DEM_ENTRIES msde1
5931: set last_refresh_number = msc_cl_refresh_s.nextval
5932: where plan_id = G_PLAN_ID
5933: --===========================================================
5934: -- Make sure that the Transaction is owned by non OEM Company
5937: and publisher_id <> G_OEM_ID
5938: and (customer_id = G_OEM_ID OR
5939: supplier_id = G_OEM_ID)
5940: and not exists ( select 1
5941: from msc_sup_dem_entries msde2
5942: where
5943: --==================================================
5944: -- Make sure that OEM has transaction for that Item.
5945: --==================================================