[Home] [Help]
9: |FILE NAME : CTOMSUTB.pls
10: |
11: |DESCRIPTION : Contains modules to :
12: | 1. Populate temporary tables bom_cto_order_lines and
13: | bom_cto_src_orgs, used for intermediate CTO processing
14: | 2. Update these tables with the config_item_id
15: | 3. Copy sourcing rule assignments from model to config item
16: |
17: |HISTORY : Created on 04-OCT-2003 by Sushant Sawant
125:
126:
127: /*--------------------------------------------------------------------------+
128: This function identifies the model items for which configuration items need
129: to be created and populates the temporary table bom_cto_src_orgs with all the
130: organizations that each configuration item needs to be created in.
131: +-------------------------------------------------------------------------*/
132:
133: FUNCTION Populate_Src_Orgs(pTopAtoLineId in number,
158: order by plan_level;
159:
160: cursor c_parent_src_orgs is
161: select distinct bcso.organization_id
162: from bom_cto_src_orgs bcso,
163: bom_cto_order_lines bcol
164: where bcol.line_id = lLineId
165: and bcol.parent_ato_line_id = bcso.line_id
166: and ( bcso.create_bom = 'Y' or bcso.organization_type in ( '3' , '4') ) ; /* 3598139 Buy Models may not have a bom */
173: create_bom,
174: create_src_rules,
175: organization_type,
176: group_reference_id
177: from bom_cto_src_orgs
178: where top_model_line_id = pTopAtoLineId;
179:
180:
181: cursor get_each_type1_model is
203: END IF;
204:
205: --
206: -- For each model item in all possible receiving orgs, call
207: -- get_all_item_orgs to populate bom_cto_src_orgs
208: --
209:
210: lStmtNumber := 20;
211: IF PG_DEBUG <> 0 THEN
419:
420:
421: if( v_model_lines.config_creation = 3 and v_model_lines.perform_match in ( 'Y', 'U' ) ) then
422:
423: select group_reference_id into v_group_reference_id from bom_cto_src_orgs_b
424: where line_id = v_model_lines.line_id ;
425:
426: update bom_cto_model_orgs set create_bom = 'N'
427: where group_reference_id = v_group_reference_id ;
429: oe_debug_pub.add( 'UPDATED BCMO create_bom = N for line id ' || v_model_lines.line_id || ' rows ' || SQL%ROWCOUNT , 1 ) ;
430: else
431:
432:
433: update bom_cto_src_orgs_b set create_bom = 'N'
434: where line_id = v_model_lines.line_id ;
435:
436: oe_debug_pub.add( 'UPDATED BCSO create_bom = N for line id ' || v_model_lines.line_id || ' rows ' || SQL%ROWCOUNT , 1 ) ;
437:
445: oe_debug_pub.add( 'OSS ORGS for Create BOM ' || v_orgs_list(i) , 1 ) ;
446:
447: if( v_model_lines.config_creation = 3 and v_model_lines.perform_match in ( 'Y', 'U' ) ) then
448:
449: select group_reference_id into v_group_reference_id from bom_cto_src_orgs_b
450: where line_id = v_model_lines.line_id ;
451:
452: update bom_cto_model_orgs set create_bom = 'Y'
453: where group_reference_id = v_group_reference_id and organization_id = v_orgs_list(i)
461:
462: else
463:
464:
465: update bom_cto_src_orgs_b set create_bom = 'Y'
466: where line_id = v_model_lines.line_id and organization_id = v_orgs_list(i)
467: -- bugfix 4274446 : Check create_config_bom parameter
468: and exists
469: ( select 1 from bom_parameters
624:
625: oe_debug_pub.add( '$$$$$$$$ Going to update Create BOM flag for Shared Costing Organizations ' , 1 ) ;
626:
627: /*Update Create_BOM Flag for Shared Costing Organizations */
628: update bom_cto_src_orgs_b bcso_b1 set create_bom = 'Y'
629: where ( organization_id , line_id ) in ( select mp.cost_organization_id , bcso_b.line_id
630: from mtl_parameters mp, bom_cto_src_orgs_b bcso_b, bom_cto_order_lines bcol
631: where bcso_b.top_model_line_id = pTopAtoLineId
632: and bcol.ato_line_id = pTopAtoLineId
626:
627: /*Update Create_BOM Flag for Shared Costing Organizations */
628: update bom_cto_src_orgs_b bcso_b1 set create_bom = 'Y'
629: where ( organization_id , line_id ) in ( select mp.cost_organization_id , bcso_b.line_id
630: from mtl_parameters mp, bom_cto_src_orgs_b bcso_b, bom_cto_order_lines bcol
631: where bcso_b.top_model_line_id = pTopAtoLineId
632: and bcol.ato_line_id = pTopAtoLineId
633: and bcol.line_id = bcso_b.line_id
634: and bcol.config_creation in ( 1 , 2 )
710:
711:
712: /*--------------------------------------------------------------------------+
713: This function identifies the model items for which configuration items need
714: to be created and populates the temporary table bom_cto_src_orgs with all the
715: organizations that each configuration item needs to be created in.
716: This will be called by Upgrade batch program.
717: +-------------------------------------------------------------------------*/
718:
744: order by plan_level;
745:
746: cursor c_parent_src_orgs is
747: select distinct bcso.organization_id
748: from bom_cto_src_orgs bcso,
749: bom_cto_order_lines_upg bcol
750: where bcol.line_id = lLineId
751: and bcol.parent_ato_line_id = bcso.line_id
752: and bcso.create_bom = 'Y';
757: rcv_org_id,
758: organization_id,
759: create_bom,
760: create_src_rules
761: from bom_cto_src_orgs
762: where top_model_line_id = pTopAtoLineId;
763:
764:
765: cursor get_each_type1_model is
791: END IF;
792:
793: --
794: -- For each model item in all possible receiving orgs, call
795: -- get_all_item_orgs to populate bom_cto_src_orgs
796: --
797:
798: lStmtNumber := 20;
799: IF PG_DEBUG <> 0 THEN
989:
990:
991: if( v_model_lines.config_creation = 3 and v_model_lines.perform_match in ( 'Y', 'U' ) ) then
992:
993: select group_reference_id into v_group_reference_id from bom_cto_src_orgs_b
994: where line_id = v_model_lines.line_id ;
995:
996: update bom_cto_model_orgs set create_bom = 'N'
997: where group_reference_id = v_group_reference_id ;
999: oe_debug_pub.add( 'UPDATED BCMO create_bom = N for line id ' || v_model_lines.line_id || ' rows ' || SQL%ROWCOUNT , 1 ) ;
1000: else
1001:
1002:
1003: update bom_cto_src_orgs_b set create_bom = 'N'
1004: where line_id = v_model_lines.line_id ;
1005:
1006: oe_debug_pub.add( 'UPDATED BCSO create_bom = N for line id ' || v_model_lines.line_id || ' rows ' || SQL%ROWCOUNT , 1 ) ;
1007:
1015: oe_debug_pub.add( 'OSS ORGS for Create BOM ' || v_orgs_list(i) , 1 ) ;
1016:
1017: if( v_model_lines.config_creation = 3 and v_model_lines.perform_match in ( 'Y', 'U' ) ) then
1018:
1019: select group_reference_id into v_group_reference_id from bom_cto_src_orgs_b
1020: where line_id = v_model_lines.line_id ;
1021:
1022: update bom_cto_model_orgs set create_bom = 'Y'
1023: where group_reference_id = v_group_reference_id and organization_id = v_orgs_list(i)
1031:
1032: else
1033:
1034:
1035: update bom_cto_src_orgs_b set create_bom = 'Y'
1036: where line_id = v_model_lines.line_id and organization_id = v_orgs_list(i)
1037: -- bugfix 4274446 : Check create_config_bom parameter
1038: and exists
1039: ( select 1 from bom_parameters
1088:
1089: oe_debug_pub.add( '$$$$$$$$ Going to update Create BOM flag for Shared Costing Organizations ' , 1 ) ;
1090:
1091: /*Update Create_BOM Flag for Shared Costing Organizations */
1092: update bom_cto_src_orgs_b bcso_b1 set create_bom = 'Y'
1093: where ( organization_id , line_id ) in ( select mp.cost_organization_id , bcso_b.line_id
1094: from mtl_parameters mp, bom_cto_src_orgs_b bcso_b, bom_cto_order_lines bcol
1095: where bcso_b.top_model_line_id = pTopAtoLineId
1096: and bcol.ato_line_id = pTopAtoLineId
1090:
1091: /*Update Create_BOM Flag for Shared Costing Organizations */
1092: update bom_cto_src_orgs_b bcso_b1 set create_bom = 'Y'
1093: where ( organization_id , line_id ) in ( select mp.cost_organization_id , bcso_b.line_id
1094: from mtl_parameters mp, bom_cto_src_orgs_b bcso_b, bom_cto_order_lines bcol
1095: where bcso_b.top_model_line_id = pTopAtoLineId
1096: and bcol.ato_line_id = pTopAtoLineId
1097: and bcol.line_id = bcso_b.line_id
1098: and bcol.config_creation in ( 1 , 2 )
1272: END Populate_Src_Orgs_Upg;
1273:
1274:
1275: /*--------------------------------------------------------------------------+
1276: This function populates the table bom_cto_src_orgs with all the organizations
1277: in which a configuration item needs to be created.
1278: The organizations include all potential sourcing orgs, receiving orgs,
1279: OE validation org and PO validation org.
1280: The line_id, rcv_org_id, organization_id combination is unique.
1345: v_source_type_code oe_order_lines_all.source_type_code%type ;
1346:
1347: CURSOR c_circular_src IS
1348: select 'Y'
1349: from bom_cto_src_orgs bcso
1350: where line_id = pLineId
1351: and model_item_id = pModelItemId
1352: and rcv_org_id = l_curr_src_org;
1353:
1373: gLoginId := nvl(fnd_global.login_id, -1);
1374:
1375: /* get top model's ato_line_id */
1376: --
1377: -- The column top_model_line_id in bom_cto_src_orgs is being used
1378: -- to store the ato_line_id of the top ATO model
1379: -- This change was required in order to support multiple ATO models
1380: -- under a PTO model
1381: --
1415:
1416: BEGIN
1417: Select organization_type
1418: Into l_source_type
1419: from bom_cto_src_orgs bcso
1420: where bcso.line_id = l_parent_ato_line_id
1421: and ( bcso.create_bom = 'Y' or bcso.organization_type in ( '3', '2'))
1422: and organization_id = pRcvOrgId ;
1423: EXCEPTION
1507:
1508:
1509:
1510: lStmtNumber := 220;
1511: insert into bom_cto_src_orgs_b
1512: (
1513: top_model_line_id,
1514: line_id,
1515: model_item_id,
1559: and pModelItemId = bbom.assembly_item_id (+)
1560: and bbom.alternate_bom_designator is null
1561: and NOT EXISTS
1562: (select NULL
1563: from bom_cto_src_orgs_b
1564: where line_id = pLineId
1565: and organization_id = pRcvOrgId
1566: and rcv_org_id = pRcvOrgId
1567: and model_item_id = pModelItemId);
1642: l_curr_rank := null ;
1643:
1644: lStmtNumber := 70;
1645:
1646: insert into bom_cto_src_orgs_b
1647: (
1648: top_model_line_id,
1649: line_id,
1650: model_item_id,
1692: and pModelItemId = bbom.assembly_item_id (+)
1693: and bbom.alternate_bom_designator is null
1694: and NOT EXISTS
1695: (select NULL
1696: from bom_cto_src_orgs_b
1697: where line_id = pLineId
1698: and organization_id = pRcvOrgId
1699: and model_item_id = pModelItemId);
1700:
1767: IF lMrpAssignmentSet is not null THEN
1768:
1769: --
1770: -- If mrp_sources_v does not insert any rows into
1771: -- bom_cto_src_orgs, this means that no sourcing rules are set-up
1772: -- for this model item in this org. Assuming that in this case
1773: -- the item in this org is sourced from itself, inserting a row
1774: -- with the receiving org as the sourcing org
1775:
1774: -- with the receiving org as the sourcing org
1775:
1776:
1777: lStmtNumber := 160;
1778: insert into bom_cto_src_orgs_b
1779: (
1780: top_model_line_id,
1781: line_id,
1782: model_item_id,
1824: and pModelItemId = bbom.assembly_item_id (+)
1825: and bbom.alternate_bom_designator is null
1826: and NOT EXISTS
1827: (select NULL
1828: from bom_cto_src_orgs_b
1829: where line_id = pLineId
1830: and organization_id = pRcvOrgId
1831: and rcv_org_id = pRcvOrgId
1832: and model_item_id = pModelItemId);
1870: else
1871:
1872: --
1873: -- If mrp_sources_v does not insert any rows into
1874: -- bom_cto_src_orgs, this means that no sourcing rules are set-up
1875: -- for this model item in this org. Assuming that in this case
1876: -- the item in this org is sourced from itself, inserting a row
1877: -- with the receiving org as the sourcing org
1878:
2224: oe_debug_pub.add( 'going to insert bcso for type 1,2 ' , 1 ) ;
2225: END IF;
2226:
2227: lStmtNum := 10 ;
2228: insert into bom_cto_src_orgs_b
2229: (
2230: top_model_line_id,
2231: line_id,
2232: model_item_id,
2272: from bom_parameters bp
2273: where bp.organization_id = l_curr_src_org
2274: and NOT EXISTS /* NOT EXISTS should be there to check whether same org is reached thru other paths */
2275: (select NULL
2276: from bom_cto_src_orgs_b
2277: where line_id = p_line_id
2278: and rcv_org_id = p_organization_id
2279: and organization_id = l_curr_src_org
2280: and organization_type = l_source_type
2322:
2323:
2324: lStmtNum := 30 ;
2325:
2326: insert into bom_cto_src_orgs_b
2327: (
2328: top_model_line_id,
2329: line_id,
2330: model_item_id,
2370: from bom_parameters bp
2371: where bp.organization_id = l_curr_src_org
2372: and NOT EXISTS /* NOT EXISTS should be there to check whether same org is reached thru other paths */
2373: (select NULL
2374: from bom_cto_src_orgs_b
2375: where line_id = p_line_id
2376: and rcv_org_id = p_organization_id
2377: and organization_id = l_curr_src_org
2378: and organization_type = l_source_type
2519: l_curr_rank := null ;
2520:
2521: lStmtNumber := 90;
2522:
2523: insert into bom_cto_src_orgs_b
2524: (
2525: top_model_line_id,
2526: line_id,
2527: model_item_id,
2570: and bp.organization_id = bom.organization_id(+)
2571: and bom.alternate_bom_designator is null
2572: and NOT EXISTS /* NOT EXISTS should be there to check whether same org is reached thru other paths */
2573: (select NULL
2574: from bom_cto_src_orgs_b
2575: where line_id = p_line_id
2576: and rcv_org_id = p_organization_id
2577: and organization_id = p_organization_id
2578: and organization_type = l_source_type
4046: loop
4047:
4048: oe_debug_pub.add( '$$$ VAL ORGS ' || p_t_org_list(i) , 1 ) ;
4049:
4050: insert into bom_cto_src_orgs_b
4051: (
4052: top_model_line_id,
4053: line_id,
4054: model_item_id,
4094: sysdate -- program_update_date
4095: from dual
4096: where NOT EXISTS
4097: (select NULL
4098: from bom_cto_src_orgs_b
4099: where line_id = p_model_line_id
4100: and model_item_id = p_model_item_id
4101: and organization_id = p_t_org_list(i) );
4102:
4137:
4138:
4139:
4140:
4141: insert into bom_cto_src_orgs_b
4142: (
4143: top_model_line_id,
4144: line_id,
4145: model_item_id,
4187: from mtl_system_items msi
4188: where msi.inventory_item_id = p_model_item_id
4189: and NOT EXISTS
4190: (select NULL
4191: from bom_cto_src_orgs_b
4192: where line_id = p_model_line_id
4193: and model_item_id = msi.inventory_item_id
4194: and organization_id = msi.organization_id );
4195:
4211:
4212: oe_debug_pub.add( '$$$ insert type3 bcso ' , 1 ) ;
4213:
4214:
4215: insert into bom_cto_src_orgs_b
4216: (
4217: top_model_line_id,
4218: line_id,
4219: model_item_id,
4263: and bom.alternate_bom_designator is null
4264: and msi.organization_id = bp.organization_id (+) /* added for bug 3504744 */
4265: and NOT EXISTS
4266: (select NULL
4267: from bom_cto_src_orgs_b
4268: where line_id = p_model_line_id
4269: and model_item_id = msi.inventory_item_id
4270: and organization_id = msi.organization_id );
4271:
4353:
4354:
4355: oe_debug_pub.add( '$$$ insert type3 bcmo bcmo ' || SQL%rowcount , 1 ) ;
4356:
4357: insert into bom_cto_src_orgs_b
4358: (
4359: top_model_line_id,
4360: line_id,
4361: group_reference_id,
4400: sysdate -- program_update_date
4401: from dual
4402: where NOT EXISTS
4403: (select NULL
4404: from bom_cto_src_orgs_b
4405: where line_id = p_model_line_id );
4406:
4407:
4408:
4502:
4503: end ;
4504:
4505:
4506: insert into bom_cto_src_orgs_b
4507: (
4508: top_model_line_id,
4509: line_id,
4510: group_reference_id,
4549: sysdate -- program_update_date
4550: from dual
4551: where NOT EXISTS
4552: (select NULL
4553: from bom_cto_src_orgs_b
4554: where line_id = p_model_line_id );
4555:
4556:
4557:
4586: x_msg_data OUT NOCOPY VARCHAR2
4587: ) IS
4588:
4589: lOperUnit inv_organization_info_v.operating_unit%TYPE;
4590: xModelItemId bom_cto_src_orgs.model_item_id%TYPE;
4591: l_model_vendors PO_AUTOSOURCE_SV.vendor_record_details;
4592: l_doc_header_id Number;
4593: l_doc_type_code Varchar2(20);
4594: l_doc_line_num Number;
4615:
4616: /* Get all bcso orgs . This is needed determine final xOrgList */
4617: cursor get_bcso_orgs is
4618: select organization_id bcso_org_id
4619: from bom_cto_src_orgs
4620: where line_id = pModelLineId;
4621:
4622: xOrgLst_copy CTO_MSUTIL_PUB.Org_list;
4623: begin
4657: from mtl_system_items
4658: where inventory_item_id = xModelItemId
4659: and organization_id not in (
4660: select organization_id
4661: from bom_cto_src_orgs
4662: where line_id = pModelLineId);
4663:
4664: else
4665:
4673: BULK COLLECT INTO xOrgLst
4674: from
4675: ( select distinct to_number(nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',ood.operating_unit),-99)) ou_id
4676: from inv_organization_info_v ood,
4677: bom_cto_src_orgs bcso
4678: where ( ood.organization_id = bcso.organization_id
4679: or
4680: ood.organization_id = bcso.rcv_org_id
4681: )
4686: where org_id in
4687: (
4688: select distinct ood.operating_unit
4689: from inv_organization_info_v ood,
4690: bom_cto_src_orgs bcso
4691: where ( ood.organization_id = bcso.organization_id
4692: or
4693: ood.organization_id = bcso.rcv_org_id
4694: )
4721: select 'Y' into l_chk_org
4722: from dual
4723: where EXISTS (
4724: select 1
4725: from bom_cto_src_orgs
4726: where line_id = pModelLineId
4727: and organization_type in (3,5));
4728:
4729:
4855: where enabled_flag = 'Y'
4856: and organization_id in (
4857: select odd.operating_unit
4858: from inv_organization_info_v odd,
4859: bom_cto_src_orgs bcso
4860: where bcso.line_id = pModelLineId
4861: and bcso.organization_type in (3,5)
4862: and odd.organization_id = bcso.organization_id));
4863:
5071: begin
5072:
5073: select 'Y' into v_procured_models_exist from dual
5074: where exists
5075: ( select * from bom_cto_src_orgs_b
5076: where line_id = p_line_id
5077: and model_item_id = p_model_item_id
5078: and nvl(organization_type , 2 ) in( '3' , '4' ) );
5079:
5095: v_bom_created := 'N' ;
5096:
5097: select 'Y' into v_bom_created from dual
5098: where exists
5099: ( select * from bom_cto_src_orgs_b where line_id = p_line_id
5100: and model_item_id = p_model_item_id
5101: and create_bom = 'Y' );
5102:
5103:
5122:
5123:
5124: begin
5125: select organization_id into v_receiving_org
5126: from bom_cto_src_orgs
5127: where line_id = p_line_id and organization_id = rcv_org_id
5128: and organization_type in ( '3' , '4' ) and rownum = 1 ;
5129:
5130: oe_debug_pub.add( ' Org to be Updated ' || to_char(v_receiving_org) , 1);
5142: oe_debug_pub.add( ' need to create bom in atleast one receiving org as bom flag = Y
5143: does not exist for any org ' , 1);
5144:
5145: lStmtNumber := 140;
5146: update bom_cto_src_orgs_b
5147: set create_bom = 'Y' /* , organization_type = l_source_type */
5148: where line_id = p_line_id
5149: and model_item_id = p_model_item_id
5150: and organization_id = v_receiving_org
5175:
5176: oe_debug_pub.add( ' need to create bom in any org as shipping org does not have model bom or bom param' , 1);
5177:
5178: lStmtNumber := 140;
5179: update bom_cto_src_orgs_b
5180: set create_bom = 'Y' /* , organization_type = l_source_type */
5181: where line_id = p_line_id
5182: and model_item_id = p_model_item_id
5183: and rcv_org_id in (
5234: Select distinct mp1.master_organization_id
5235: Bulk Collect into
5236: x_orgs_list
5237: from mtl_parameters mp1,
5238: bom_cto_src_orgs bcso
5239: where bcso.line_id = p_model_line_id
5240: and bcso.organization_id = mp1.organization_id
5241: and mp1.master_organization_id not in
5242: ( Select organization_id
5239: where bcso.line_id = p_model_line_id
5240: and bcso.organization_id = mp1.organization_id
5241: and mp1.master_organization_id not in
5242: ( Select organization_id
5243: from bom_cto_src_orgs
5244: where line_id = p_model_line_id);
5245:
5246: If PG_DEBUG <> 0 Then
5247: i := x_orgs_list.first;