[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
128:
129:
130: /*--------------------------------------------------------------------------+
131: This function identifies the model items for which configuration items need
132: to be created and populates the temporary table bom_cto_src_orgs with all the
133: organizations that each configuration item needs to be created in.
134: +-------------------------------------------------------------------------*/
135:
136: FUNCTION Populate_Src_Orgs(pTopAtoLineId in number,
161: order by plan_level;
162:
163: cursor c_parent_src_orgs is
164: select distinct bcso.organization_id
165: from bom_cto_src_orgs bcso,
166: bom_cto_order_lines bcol
167: where bcol.line_id = lLineId
168: and bcol.parent_ato_line_id = bcso.line_id
169: and ( bcso.create_bom = 'Y' or bcso.organization_type in ( '3' , '4') ) ; /* 3598139 Buy Models may not have a bom */
176: create_bom,
177: create_src_rules,
178: organization_type,
179: group_reference_id
180: from bom_cto_src_orgs
181: where top_model_line_id = pTopAtoLineId;
182:
183:
184: cursor get_each_type1_model is
206: END IF;
207:
208: --
209: -- For each model item in all possible receiving orgs, call
210: -- get_all_item_orgs to populate bom_cto_src_orgs
211: --
212:
213: lStmtNumber := 20;
214: IF PG_DEBUG <> 0 THEN
422:
423:
424: if( v_model_lines.config_creation = 3 and v_model_lines.perform_match in ( 'Y', 'U' ) ) then
425:
426: select group_reference_id into v_group_reference_id from bom_cto_src_orgs_b
427: where line_id = v_model_lines.line_id ;
428:
429: update bom_cto_model_orgs set create_bom = 'N'
430: where group_reference_id = v_group_reference_id ;
432: oe_debug_pub.add( 'UPDATED BCMO create_bom = N for line id ' || v_model_lines.line_id || ' rows ' || SQL%ROWCOUNT , 1 ) ;
433: else
434:
435:
436: update bom_cto_src_orgs_b set create_bom = 'N'
437: where line_id = v_model_lines.line_id ;
438:
439: oe_debug_pub.add( 'UPDATED BCSO create_bom = N for line id ' || v_model_lines.line_id || ' rows ' || SQL%ROWCOUNT , 1 ) ;
440:
448: oe_debug_pub.add( 'OSS ORGS for Create BOM ' || v_orgs_list(i) , 1 ) ;
449:
450: if( v_model_lines.config_creation = 3 and v_model_lines.perform_match in ( 'Y', 'U' ) ) then
451:
452: select group_reference_id into v_group_reference_id from bom_cto_src_orgs_b
453: where line_id = v_model_lines.line_id ;
454:
455: update bom_cto_model_orgs set create_bom = 'Y'
456: where group_reference_id = v_group_reference_id and organization_id = v_orgs_list(i)
464:
465: else
466:
467:
468: update bom_cto_src_orgs_b set create_bom = 'Y'
469: where line_id = v_model_lines.line_id and organization_id = v_orgs_list(i)
470: -- bugfix 4274446 : Check create_config_bom parameter
471: and exists
472: ( select 1 from bom_parameters
627:
628: oe_debug_pub.add( '$$$$$$$$ Going to update Create BOM flag for Shared Costing Organizations ' , 1 ) ;
629:
630: /*Update Create_BOM Flag for Shared Costing Organizations */
631: update bom_cto_src_orgs_b bcso_b1 set create_bom = 'Y'
632: where ( organization_id , line_id ) in ( select mp.cost_organization_id , bcso_b.line_id
633: from mtl_parameters mp, bom_cto_src_orgs_b bcso_b, bom_cto_order_lines bcol
634: where bcso_b.top_model_line_id = pTopAtoLineId
635: and bcol.ato_line_id = pTopAtoLineId
629:
630: /*Update Create_BOM Flag for Shared Costing Organizations */
631: update bom_cto_src_orgs_b bcso_b1 set create_bom = 'Y'
632: where ( organization_id , line_id ) in ( select mp.cost_organization_id , bcso_b.line_id
633: from mtl_parameters mp, bom_cto_src_orgs_b bcso_b, bom_cto_order_lines bcol
634: where bcso_b.top_model_line_id = pTopAtoLineId
635: and bcol.ato_line_id = pTopAtoLineId
636: and bcol.line_id = bcso_b.line_id
637: and bcol.config_creation in ( 1 , 2 )
713:
714:
715: /*--------------------------------------------------------------------------+
716: This function identifies the model items for which configuration items need
717: to be created and populates the temporary table bom_cto_src_orgs with all the
718: organizations that each configuration item needs to be created in.
719: This will be called by Upgrade batch program.
720: +-------------------------------------------------------------------------*/
721:
747: order by plan_level;
748:
749: cursor c_parent_src_orgs is
750: select distinct bcso.organization_id
751: from bom_cto_src_orgs bcso,
752: bom_cto_order_lines_upg bcol
753: where bcol.line_id = lLineId
754: and bcol.parent_ato_line_id = bcso.line_id
755: and bcso.create_bom = 'Y';
760: rcv_org_id,
761: organization_id,
762: create_bom,
763: create_src_rules
764: from bom_cto_src_orgs
765: where top_model_line_id = pTopAtoLineId;
766:
767:
768: cursor get_each_type1_model is
796: END IF;
797:
798: --
799: -- For each model item in all possible receiving orgs, call
800: -- get_all_item_orgs to populate bom_cto_src_orgs
801: --
802:
803: lStmtNumber := 20;
804: IF PG_DEBUG <> 0 THEN
994:
995:
996: if( v_model_lines.config_creation = 3 and v_model_lines.perform_match in ( 'Y', 'U' ) ) then
997:
998: select group_reference_id into v_group_reference_id from bom_cto_src_orgs_b
999: where line_id = v_model_lines.line_id ;
1000:
1001: update bom_cto_model_orgs set create_bom = 'N'
1002: where group_reference_id = v_group_reference_id ;
1004: oe_debug_pub.add( 'UPDATED BCMO create_bom = N for line id ' || v_model_lines.line_id || ' rows ' || SQL%ROWCOUNT , 1 ) ;
1005: else
1006:
1007:
1008: update bom_cto_src_orgs_b set create_bom = 'N'
1009: where line_id = v_model_lines.line_id ;
1010:
1011: oe_debug_pub.add( 'UPDATED BCSO create_bom = N for line id ' || v_model_lines.line_id || ' rows ' || SQL%ROWCOUNT , 1 ) ;
1012:
1020: oe_debug_pub.add( 'OSS ORGS for Create BOM ' || v_orgs_list(i) , 1 ) ;
1021:
1022: if( v_model_lines.config_creation = 3 and v_model_lines.perform_match in ( 'Y', 'U' ) ) then
1023:
1024: select group_reference_id into v_group_reference_id from bom_cto_src_orgs_b
1025: where line_id = v_model_lines.line_id ;
1026:
1027: update bom_cto_model_orgs set create_bom = 'Y'
1028: where group_reference_id = v_group_reference_id and organization_id = v_orgs_list(i)
1036:
1037: else
1038:
1039:
1040: update bom_cto_src_orgs_b set create_bom = 'Y'
1041: where line_id = v_model_lines.line_id and organization_id = v_orgs_list(i)
1042: -- bugfix 4274446 : Check create_config_bom parameter
1043: and exists
1044: ( select 1 from bom_parameters
1093:
1094: oe_debug_pub.add( '$$$$$$$$ Going to update Create BOM flag for Shared Costing Organizations ' , 1 ) ;
1095:
1096: /*Update Create_BOM Flag for Shared Costing Organizations */
1097: update bom_cto_src_orgs_b bcso_b1 set create_bom = 'Y'
1098: where ( organization_id , line_id ) in ( select mp.cost_organization_id , bcso_b.line_id
1099: from mtl_parameters mp, bom_cto_src_orgs_b bcso_b, bom_cto_order_lines bcol
1100: where bcso_b.top_model_line_id = pTopAtoLineId
1101: and bcol.ato_line_id = pTopAtoLineId
1095:
1096: /*Update Create_BOM Flag for Shared Costing Organizations */
1097: update bom_cto_src_orgs_b bcso_b1 set create_bom = 'Y'
1098: where ( organization_id , line_id ) in ( select mp.cost_organization_id , bcso_b.line_id
1099: from mtl_parameters mp, bom_cto_src_orgs_b bcso_b, bom_cto_order_lines bcol
1100: where bcso_b.top_model_line_id = pTopAtoLineId
1101: and bcol.ato_line_id = pTopAtoLineId
1102: and bcol.line_id = bcso_b.line_id
1103: and bcol.config_creation in ( 1 , 2 )
1277: END Populate_Src_Orgs_Upg;
1278:
1279:
1280: /*--------------------------------------------------------------------------+
1281: This function populates the table bom_cto_src_orgs with all the organizations
1282: in which a configuration item needs to be created.
1283: The organizations include all potential sourcing orgs, receiving orgs,
1284: OE validation org and PO validation org.
1285: The line_id, rcv_org_id, organization_id combination is unique.
1349: v_source_type_code oe_order_lines_all.source_type_code%type ;
1350:
1351: CURSOR c_circular_src IS
1352: select 'Y'
1353: from bom_cto_src_orgs bcso
1354: where line_id = pLineId
1355: and model_item_id = pModelItemId
1356: and rcv_org_id = l_curr_src_org;
1357:
1377: gLoginId := nvl(fnd_global.login_id, -1);
1378:
1379: /* get top model's ato_line_id */
1380: --
1381: -- The column top_model_line_id in bom_cto_src_orgs is being used
1382: -- to store the ato_line_id of the top ATO model
1383: -- This change was required in order to support multiple ATO models
1384: -- under a PTO model
1385: --
1418:
1419: BEGIN
1420: Select organization_type
1421: Into l_source_type
1422: from bom_cto_src_orgs bcso
1423: where bcso.line_id = l_parent_ato_line_id
1424: and ( bcso.create_bom = 'Y' or bcso.organization_type in ( '3', '2'))
1425: and organization_id = pRcvOrgId ;
1426: EXCEPTION
1501:
1502:
1503:
1504: lStmtNumber := 220;
1505: insert into bom_cto_src_orgs_b
1506: (
1507: top_model_line_id,
1508: line_id,
1509: model_item_id,
1553: and pModelItemId = bbom.assembly_item_id (+)
1554: and bbom.alternate_bom_designator is null
1555: and NOT EXISTS
1556: (select NULL
1557: from bom_cto_src_orgs_b
1558: where line_id = pLineId
1559: and organization_id = pRcvOrgId
1560: and rcv_org_id = pRcvOrgId
1561: and model_item_id = pModelItemId);
1614: l_curr_rank := null ;
1615:
1616: lStmtNumber := 70;
1617:
1618: insert into bom_cto_src_orgs_b
1619: (
1620: top_model_line_id,
1621: line_id,
1622: model_item_id,
1664: and pModelItemId = bbom.assembly_item_id (+)
1665: and bbom.alternate_bom_designator is null
1666: and NOT EXISTS
1667: (select NULL
1668: from bom_cto_src_orgs_b
1669: where line_id = pLineId
1670: and organization_id = pRcvOrgId
1671: and model_item_id = pModelItemId);
1672:
1723: IF lMrpAssignmentSet is not null THEN
1724:
1725: --
1726: -- If mrp_sources_v does not insert any rows into
1727: -- bom_cto_src_orgs, this means that no sourcing rules are set-up
1728: -- for this model item in this org. Assuming that in this case
1729: -- the item in this org is sourced from itself, inserting a row
1730: -- with the receiving org as the sourcing org
1731:
1730: -- with the receiving org as the sourcing org
1731:
1732:
1733: lStmtNumber := 160;
1734: insert into bom_cto_src_orgs_b
1735: (
1736: top_model_line_id,
1737: line_id,
1738: model_item_id,
1780: and pModelItemId = bbom.assembly_item_id (+)
1781: and bbom.alternate_bom_designator is null
1782: and NOT EXISTS
1783: (select NULL
1784: from bom_cto_src_orgs_b
1785: where line_id = pLineId
1786: and organization_id = pRcvOrgId
1787: and rcv_org_id = pRcvOrgId
1788: and model_item_id = pModelItemId);
1827: -- Bugfix 16315202: This else is not needed. Added an end if before.
1828: -- else
1829: -- --
1830: -- -- If mrp_sources_v does not insert any rows into
1831: -- -- bom_cto_src_orgs, this means that no sourcing rules are set-up
1832: -- -- for this model item in this org. Assuming that in this case
1833: -- -- the item in this org is sourced from itself, inserting a row
1834: -- -- with the receiving org as the sourcing org
1835: -- null ;
2211: oe_debug_pub.add( 'going to insert bcso for type 1,2 ' , 1 ) ;
2212: END IF;
2213:
2214: lStmtNum := 10 ;
2215: insert into bom_cto_src_orgs_b
2216: (
2217: top_model_line_id,
2218: line_id,
2219: model_item_id,
2259: from bom_parameters bp
2260: where bp.organization_id = l_curr_src_org
2261: and NOT EXISTS /* NOT EXISTS should be there to check whether same org is reached thru other paths */
2262: (select NULL
2263: from bom_cto_src_orgs_b
2264: where line_id = p_line_id
2265: and rcv_org_id = p_organization_id
2266: and organization_id = l_curr_src_org
2267: and organization_type = l_source_type
2309:
2310:
2311: lStmtNum := 30 ;
2312:
2313: insert into bom_cto_src_orgs_b
2314: (
2315: top_model_line_id,
2316: line_id,
2317: model_item_id,
2357: from bom_parameters bp
2358: where bp.organization_id = l_curr_src_org
2359: and NOT EXISTS /* NOT EXISTS should be there to check whether same org is reached thru other paths */
2360: (select NULL
2361: from bom_cto_src_orgs_b
2362: where line_id = p_line_id
2363: and rcv_org_id = p_organization_id
2364: and organization_id = l_curr_src_org
2365: and organization_type = l_source_type
2506: l_curr_rank := null ;
2507:
2508: lStmtNumber := 90;
2509:
2510: insert into bom_cto_src_orgs_b
2511: (
2512: top_model_line_id,
2513: line_id,
2514: model_item_id,
2557: and bp.organization_id = bom.organization_id(+)
2558: and bom.alternate_bom_designator is null
2559: and NOT EXISTS /* NOT EXISTS should be there to check whether same org is reached thru other paths */
2560: (select NULL
2561: from bom_cto_src_orgs_b
2562: where line_id = p_line_id
2563: and rcv_org_id = p_organization_id
2564: and organization_id = p_organization_id
2565: and organization_type = l_source_type
4563: loop
4564:
4565: oe_debug_pub.add( '$$$ VAL ORGS ' || p_t_org_list(i) , 1 ) ;
4566:
4567: insert into bom_cto_src_orgs_b
4568: (
4569: top_model_line_id,
4570: line_id,
4571: model_item_id,
4611: sysdate -- program_update_date
4612: from dual
4613: where NOT EXISTS
4614: (select NULL
4615: from bom_cto_src_orgs_b
4616: where line_id = p_model_line_id
4617: and model_item_id = p_model_item_id
4618: and organization_id = p_t_org_list(i) );
4619:
4654:
4655:
4656:
4657:
4658: insert into bom_cto_src_orgs_b
4659: (
4660: top_model_line_id,
4661: line_id,
4662: model_item_id,
4704: from mtl_system_items msi
4705: where msi.inventory_item_id = p_model_item_id
4706: and NOT EXISTS
4707: (select NULL
4708: from bom_cto_src_orgs_b
4709: where line_id = p_model_line_id
4710: and model_item_id = msi.inventory_item_id
4711: and organization_id = msi.organization_id );
4712:
4728:
4729: oe_debug_pub.add( '$$$ insert type3 bcso ' , 1 ) ;
4730:
4731:
4732: insert into bom_cto_src_orgs_b
4733: (
4734: top_model_line_id,
4735: line_id,
4736: model_item_id,
4780: and bom.alternate_bom_designator is null
4781: and msi.organization_id = bp.organization_id (+) /* added for bug 3504744 */
4782: and NOT EXISTS
4783: (select NULL
4784: from bom_cto_src_orgs_b
4785: where line_id = p_model_line_id
4786: and model_item_id = msi.inventory_item_id
4787: and organization_id = msi.organization_id );
4788:
4870:
4871:
4872: oe_debug_pub.add( '$$$ insert type3 bcmo bcmo ' || SQL%rowcount , 1 ) ;
4873:
4874: insert into bom_cto_src_orgs_b
4875: (
4876: top_model_line_id,
4877: line_id,
4878: group_reference_id,
4917: sysdate -- program_update_date
4918: from dual
4919: where NOT EXISTS
4920: (select NULL
4921: from bom_cto_src_orgs_b
4922: where line_id = p_model_line_id );
4923:
4924:
4925:
5031:
5032: end ;
5033:
5034:
5035: insert into bom_cto_src_orgs_b
5036: (
5037: top_model_line_id,
5038: line_id,
5039: group_reference_id,
5078: sysdate -- program_update_date
5079: from dual
5080: where NOT EXISTS
5081: (select NULL
5082: from bom_cto_src_orgs_b
5083: where line_id = p_model_line_id );
5084:
5085: --debug 10240482
5086: lCnt := sql%rowcount;
5116: x_msg_data OUT NOCOPY VARCHAR2
5117: ) IS
5118:
5119: lOperUnit inv_organization_info_v.operating_unit%TYPE;
5120: xModelItemId bom_cto_src_orgs.model_item_id%TYPE;
5121: l_model_vendors PO_AUTOSOURCE_SV.vendor_record_details;
5122: l_doc_header_id Number;
5123: l_doc_type_code Varchar2(20);
5124: l_doc_line_num Number;
5145:
5146: /* Get all bcso orgs . This is needed determine final xOrgList */
5147: cursor get_bcso_orgs is
5148: select organization_id bcso_org_id
5149: from bom_cto_src_orgs
5150: where line_id = pModelLineId;
5151:
5152: xOrgLst_copy CTO_MSUTIL_PUB.Org_list;
5153: begin
5187: from mtl_system_items
5188: where inventory_item_id = xModelItemId
5189: and organization_id not in (
5190: select organization_id
5191: from bom_cto_src_orgs
5192: where line_id = pModelLineId);
5193:
5194: else
5195:
5203: BULK COLLECT INTO xOrgLst
5204: from
5205: ( select distinct to_number(nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',ood.operating_unit),-99)) ou_id
5206: from inv_organization_info_v ood,
5207: bom_cto_src_orgs bcso
5208: where ( ood.organization_id = bcso.organization_id
5209: or
5210: ood.organization_id = bcso.rcv_org_id
5211: )
5216: where org_id in
5217: (
5218: select distinct ood.operating_unit
5219: from inv_organization_info_v ood,
5220: bom_cto_src_orgs bcso
5221: where ( ood.organization_id = bcso.organization_id
5222: or
5223: ood.organization_id = bcso.rcv_org_id
5224: )
5251: select 'Y' into l_chk_org
5252: from dual
5253: where EXISTS (
5254: select 1
5255: from bom_cto_src_orgs
5256: where line_id = pModelLineId
5257: and organization_type in (3,5));
5258:
5259:
5385: where enabled_flag = 'Y'
5386: and organization_id in (
5387: select odd.operating_unit
5388: from inv_organization_info_v odd,
5389: bom_cto_src_orgs bcso
5390: where bcso.line_id = pModelLineId
5391: and bcso.organization_type in (3,5)
5392: and odd.organization_id = bcso.organization_id));
5393:
5601: begin
5602:
5603: select 'Y' into v_procured_models_exist from dual
5604: where exists
5605: ( select * from bom_cto_src_orgs_b
5606: where line_id = p_line_id
5607: and model_item_id = p_model_item_id
5608: and nvl(organization_type , 2 ) in( '3' , '4' ) );
5609:
5625: v_bom_created := 'N' ;
5626:
5627: select 'Y' into v_bom_created from dual
5628: where exists
5629: ( select * from bom_cto_src_orgs_b where line_id = p_line_id
5630: and model_item_id = p_model_item_id
5631: and create_bom = 'Y' );
5632:
5633:
5652:
5653:
5654: begin
5655: select organization_id into v_receiving_org
5656: from bom_cto_src_orgs
5657: where line_id = p_line_id and organization_id = rcv_org_id
5658: and organization_type in ( '3' , '4' ) and rownum = 1 ;
5659:
5660: oe_debug_pub.add( ' Org to be Updated ' || to_char(v_receiving_org) , 1);
5672: oe_debug_pub.add( ' need to create bom in atleast one receiving org as bom flag = Y
5673: does not exist for any org ' , 1);
5674:
5675: lStmtNumber := 140;
5676: update bom_cto_src_orgs_b
5677: set create_bom = 'Y' /* , organization_type = l_source_type */
5678: where line_id = p_line_id
5679: and model_item_id = p_model_item_id
5680: and organization_id = v_receiving_org
5705:
5706: oe_debug_pub.add( ' need to create bom in any org as shipping org does not have model bom or bom param' , 1);
5707:
5708: lStmtNumber := 140;
5709: update bom_cto_src_orgs_b
5710: set create_bom = 'Y' /* , organization_type = l_source_type */
5711: where line_id = p_line_id
5712: and model_item_id = p_model_item_id
5713: and rcv_org_id in (
5764: Select distinct mp1.master_organization_id
5765: Bulk Collect into
5766: x_orgs_list
5767: from mtl_parameters mp1,
5768: bom_cto_src_orgs bcso
5769: where bcso.line_id = p_model_line_id
5770: and bcso.organization_id = mp1.organization_id
5771: and mp1.master_organization_id not in
5772: ( Select organization_id
5769: where bcso.line_id = p_model_line_id
5770: and bcso.organization_id = mp1.organization_id
5771: and mp1.master_organization_id not in
5772: ( Select organization_id
5773: from bom_cto_src_orgs
5774: where line_id = p_model_line_id);
5775:
5776: If PG_DEBUG <> 0 Then
5777: i := x_orgs_list.first;