[Home] [Help]
9: |FILE NAME : CTOUTILB.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-MAY-2000 by Sajani Sheth
202:
203:
204: /*--------------------------------------------------------------------------+
205: This function identifies the model items for which configuration items need
206: to be created and populates the temporary table bom_cto_src_orgs with all the
207: organizations that each configuration item needs to be created in.
208: +-------------------------------------------------------------------------*/
209:
210: FUNCTION Populate_Src_Orgs(pTopAtoLineId in number,
231: order by plan_level;
232:
233: cursor c_parent_src_orgs is
234: select distinct bcso.organization_id
235: from bom_cto_src_orgs bcso,
236: bom_cto_order_lines bcol
237: where bcol.line_id = lLineId
238: and bcol.parent_ato_line_id = bcso.line_id
239: and bcso.create_bom = 'Y';
244: rcv_org_id,
245: organization_id,
246: create_bom,
247: create_src_rules
248: from bom_cto_src_orgs
249: where top_model_line_id = pTopAtoLineId;
250:
251: BEGIN
252:
255: END IF;
256:
257: --
258: -- For each model item in all possible receiving orgs, call
259: -- get_all_item_orgs to populate bom_cto_src_orgs
260: --
261:
262: lStmtNumber := 20;
263: IF PG_DEBUG <> 0 THEN
431:
432:
433:
434: /*--------------------------------------------------------------------------+
435: This function populates the table bom_cto_src_orgs with all the organizations
436: in which a configuration item needs to be created.
437: The organizations include all potential sourcing orgs, receiving orgs,
438: OE validation org and PO validation org.
439: The line_id, rcv_org_id, organization_id combination is unique.
502: v_source_type_code oe_order_lines_all.source_type_code%type ;
503:
504: CURSOR c_circular_src IS
505: select 'Y'
506: from bom_cto_src_orgs bcso
507: where line_id = pLineId
508: and model_item_id = pModelItemId
509: and rcv_org_id = l_curr_src_org;
510:
521: gLoginId := nvl(fnd_global.login_id, -1);
522:
523: /* get top model's ato_line_id */
524: --
525: -- The column top_model_line_id in bom_cto_src_orgs is being used
526: -- to store the ato_line_id of the top ATO model
527: -- This change was required in order to support multiple ATO models
528: -- under a PTO model
529: --
561:
562: BEGIN
563: Select organization_type
564: Into l_source_type
565: from bom_cto_src_orgs bcso
566: where bcso.line_id = l_parent_ato_line_id
567: and bcso.create_bom = 'Y';
568: EXCEPTION WHEN NO_DATA_FOUND THEN
569: Null;
713:
714: --
715: -- For the given model_item_id, receiving org and default
716: -- assignment set, traverse the chain of sourcing rules and
717: -- populate bom_cto_src_orgs with each source, till the final
718: -- source is reached.
719: -- Set the create_bom flag in the final source only
720: -- Error out if multiple or circular src rules are found
721: --
944:
945: /* This statement needs to be there to account for all passess except first pass */
946:
947: lStmtNumber := 60;
948: update bom_cto_src_orgs
949: set create_bom = 'Y',organization_type = l_source_type
950: where line_id = pLineId
951: and model_item_id = pModelItemId
952: and organization_id = l_curr_src_org;
980: raise FND_API.G_EXC_ERROR;
981: END IF;
982:
983: lStmtNumber := 120;
984: insert into bom_cto_src_orgs
985: (
986: top_model_line_id,
987: line_id,
988: model_item_id,
1041:
1042: --- The source type column is added by Renga Kannan on 08/21/01
1043:
1044: lStmtNumber := 140;
1045: update bom_cto_src_orgs
1046: set create_bom = 'Y', organization_type = l_source_type
1047: where line_id = pLineId
1048: and model_item_id = pModelItemId
1049: and organization_id = l_curr_src_org
1067: END IF; /* check for DROP SHIP , BUY_ITEM_FLAG is not Y */
1068:
1069: --
1070: -- If mrp_sources_v does not insert any rows into
1071: -- bom_cto_src_orgs, this means that no sourcing rules are set-up
1072: -- for this model item in this org. Assuming that in this case
1073: -- the item in this org is sourced from itself, inserting a row
1074: -- with the receiving org as the sourcing org
1075:
1073: -- the item in this org is sourced from itself, inserting a row
1074: -- with the receiving org as the sourcing org
1075:
1076: lStmtNumber := 150;
1077: insert into bom_cto_src_orgs
1078: (
1079: top_model_line_id,
1080: line_id,
1081: model_item_id,
1118: sysdate -- program_update_date
1119: from dual
1120: where NOT EXISTS
1121: (select NULL
1122: from bom_cto_src_orgs
1123: where line_id = pLineId
1124: and model_item_id = pModelItemId);
1125:
1126: IF PG_DEBUG <> 0 THEN
1127: oe_debug_pub.add('populate_plan_level: ' || 'after insert 2',2);
1128: END IF;
1129:
1130: --
1131: -- Next, populate bom_cto_src_orgs with the
1132: -- receiving org for the model item
1133: -- If the org already exists for this item,
1134: -- do nothing, else insert a new row
1135:
1136: -- Added by Renga Kannan to include one more column source_type to it.
1137:
1138: lStmtNumber := 160;
1139:
1140: insert into bom_cto_src_orgs
1141: (
1142: top_model_line_id,
1143: line_id,
1144: model_item_id,
1181: sysdate -- program_update_date
1182: from dual
1183: where NOT EXISTS
1184: (select NULL
1185: from bom_cto_src_orgs
1186: where line_id = pLineId
1187: and model_item_id = pModelItemId
1188: and organization_id = pRcvOrgId);
1189:
1192: END IF;
1193:
1194: --
1195: -- Next, get the OE validation org and populate it in
1196: -- bom_cto_src_orgs
1197: -- If the org already exists for this item,
1198: -- do nothing, else insert a new row
1199: --
1200: lStmtNumber := 170;
1249: END IF;
1250:
1251: lStmtNumber := 190;
1252:
1253: insert into bom_cto_src_orgs
1254: (
1255: top_model_line_id,
1256: line_id,
1257: model_item_id,
1294: sysdate -- program_update_date
1295: from dual
1296: where NOT EXISTS
1297: (select NULL
1298: from bom_cto_src_orgs
1299: where line_id = pLineId
1300: and model_item_id = pModelItemId
1301: and organization_id = lVAlidationOrg);
1302:
1305: END IF;
1306:
1307: --
1308: -- Next, get the PO validation org and populate it in
1309: -- bom_cto_src_orgs. If PO val org is -99, (bugfix 2646849: changed from 0 to -99) null or not setup,
1310: -- we will not populate a row for PO val org, and will not
1311: -- error out.
1312: -- If the org already exists for this item,
1313: -- do nothing, else insert a new row
1347: -- inv_organziation_info_v
1348:
1349: select nvl(fsp.inventory_organization_id,-99) --bugfix 2646849: 0 can be a valid orgn_id.
1350: into lPoValidationOrg
1351: from bom_cto_src_orgs bcso,
1352: financials_system_params_all fsp,
1353: inv_organization_info_v org
1354: where bcso.line_id = pLineId
1355: and bcso.create_bom = 'Y'
1402: END IF;
1403:
1404: lStmtNumber := 210;
1405:
1406: insert into bom_cto_src_orgs
1407: (
1408: top_model_line_id,
1409: line_id,
1410: model_item_id,
1447: sysdate -- program_update_date
1448: from dual
1449: where NOT EXISTS
1450: (select NULL
1451: from bom_cto_src_orgs
1452: where line_id = pLineId
1453: and model_item_id = pModelItemId
1454: and organization_id = lPoVAlidationOrg);
1455:
1462: -- apart from enabling the config in all the sourcing chain orgs, we need
1463: -- to enable it in all the org where model item is active
1464: -- This is used for global purchase agreement
1465:
1466: insert into bom_cto_src_orgs
1467: (
1468: top_model_line_id,
1469: line_id,
1470: model_item_id,
1509: where inventory_item_id = pModelItemId
1510: and INVENTORY_ITEM_STATUS_CODE = 'Active'
1511: and organization_id not in
1512: (select organization_id
1513: from bom_cto_src_orgs
1514: where line_id = plineid
1515: and top_model_line_id = lTopAtoLineId);
1516:
1517: EXCEPTION
1620: END Update_Order_Lines;
1621:
1622:
1623: /*--------------------------------------------------------------------------+
1624: This function updates table bom_cto_src_orgs with the config_item_id for
1625: a given model item.
1626: It is called by "Match" and "Create_Item" programs.
1627: +-------------------------------------------------------------------------*/
1628: FUNCTION Update_Src_Orgs(pLineId in number,
1636: --
1637: -- Update all lines for the model item with the config item id
1638: --
1639:
1640: update bom_cto_src_orgs
1641: set config_item_id = pConfigId
1642: where line_id = pLineId
1643: and model_item_id = pModelId;
1644:
4250: *********************************************************************************************************/
4251:
4252:
4253: PROCEDURE GENERATE_BOM_ATTACH_TEXT
4254: (p_line_id bom_cto_src_orgs.line_id%type,
4255: x_text in out NOCOPY long,
4256: x_return_status out NOCOPY varchar2
4257: ) is
4258: