[Home] [Help]
28: Populate the user-friendly columns to bill record in the interface table
29: REQUIRES
30:
31: MODIFIES
32: BOM_BILL_OF_MTLS_INTERFACE
33: MTL_INTERFACE_ERRORS
34: RETURNS
35: 0 if successful
36: SQLCODE if unsuccessful
61:
62: stmt_num := 1;
63: /* Resolve the Bill sequence ids for updates and deletes */
64:
65: UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
66: SET(assembly_item_id, organization_id, alternate_bom_designator)
67: = (SELECT assembly_item_id, organization_id , alternate_bom_designator
68: FROM bom_structures_b BBM1
69: WHERE BBM1.bill_sequence_id = BBMI.bill_sequence_id)
85:
86: /* Update Organization Code using Organization_id
87: this also needed if Organization_id is given and code is not given*/
88:
89: UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
90: SET organization_code = (SELECT organization_code
91: FROM MTL_PARAMETERS MP1
92: WHERE mp1.organization_id = BBMI.organization_id)
93: WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
107: stmt_num := 3;
108: /* Update Organization_ids if organization code is given org id is null.
109: Orgnaization_id information is needed in the next steps */
110:
111: UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
112: SET organization_id = (SELECT organization_id
113: FROM MTL_PARAMETERS mp1
114: WHERE mp1.organization_code = BBMI.organization_code)
115: WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
123: );
124:
125: stmt_num := 3.1;
126: /* Update Assembly Item name */
127: UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
128: SET item_number = (SELECT concatenated_segments
129: FROM MTL_SYSTEM_ITEMS_KFV mvl1
130: WHERE mvl1.inventory_item_id = BBMI.assembly_item_id
131: and mvl1.organization_id = BBMI.organization_id)
145:
146: stmt_num := 4;
147:
148: /* Update Assembly Item Id*/
149: UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
150: SET assembly_item_id = (SELECT inventory_item_id
151: FROM MTL_SYSTEM_ITEMS_KFV mvl1
152: WHERE mvl1.concatenated_segments = BBMI.item_number
153: AND mvl1.organization_id = BBMI.organization_id)
164:
165: stmt_num := 5;
166: /* Assign transaction ids */
167:
168: UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
169: SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
170: transaction_type = upper(transaction_type)
171: WHERE transaction_id is null
172: AND upper(transaction_type) in (G_Create, G_Update, G_Delete,'NO_OP')
185: stmt_num := 6;
186: /* Assign Common Item id and Common Organization id if common_bill_sequence_id is given
187: and a bill exists with that bill_sequence_id */
188:
189: UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
190: SET(common_assembly_item_id, common_organization_id)
191: = (SELECT assembly_item_id, organization_id
192: FROM bom_structures_b BBM1
193: WHERE BBM1.bill_sequence_id = BBMI.common_bill_sequence_id)
207:
208: stmt_num :=7;
209: /* Assign common_organization_code if common_organization_id is populated */
210:
211: UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
212: SET common_org_code = (SELECT organization_code
213: FROM MTL_PARAMETERS mp1
214: WHERE mp1.organization_id = BBMI.common_organization_id)
215: WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
229: stmt_num :=8 ;
230: /* Update Organization_ids if organization_code is given org id is null.
231: Orgnaization_id information is needed in the next steps */
232:
233: UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
234: SET common_organization_id = (SELECT organization_id
235: FROM MTL_PARAMETERS MP1
236: WHERE mp1.organization_code = BBMI.common_org_code)
237: WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
246:
247: stmt_num := 9;
248: /* Update Assembly Item name */
249:
250: UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
251: SET common_item_number = (SELECT concatenated_segments
252: FROM MTL_SYSTEM_ITEMS_KFV mvl1
253: WHERE mvl1.inventory_item_id = BBMI.common_assembly_item_id
254: AND mvl1.organization_id = BBMI.common_organization_id)
267: AND mvl2.organization_id = BBMI.common_organization_id);
268:
269: /* Update the delete_group_name from bom_interface_delete_groups */
270: stmt_num := 9.5;
271: UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
272: SET (DELETE_GROUP_NAME, DG_DESCRIPTION)
273: = (SELECT DELETE_GROUP_NAME, DESCRIPTION
274: FROM bom_interface_delete_groups
275: Where upper(entity_name) = 'BOM_BILL_OF_MTLS_INTERFACE'
271: UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
272: SET (DELETE_GROUP_NAME, DG_DESCRIPTION)
273: = (SELECT DELETE_GROUP_NAME, DESCRIPTION
274: FROM bom_interface_delete_groups
275: Where upper(entity_name) = 'BOM_BILL_OF_MTLS_INTERFACE'
276: And rownum = 1)
277: WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
278: AND upper(transaction_type) in (G_Delete)
279: AND organization_id is not null
284: OR ( p_batch_id = BBMI.batch_id )
285: )
286: AND exists (SELECT 'x'
287: FROM bom_interface_delete_groups
288: Where upper(entity_name) = 'BOM_BILL_OF_MTLS_INTERFACE'
289: );
290:
291: stmt_num := 9.6;
292: /* Update Bill Sequence Id when there are IDs available */
290:
291: stmt_num := 9.6;
292: /* Update Bill Sequence Id when there are IDs available */
293:
294: UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
295: SET bill_sequence_id = (SELECT bill_sequence_id
296: FROM bom_structures_b bom
297: WHERE bom.assembly_item_id = BBMI.assembly_item_id
298: AND bom.organization_id = BBMI.organization_id
315: /* Commented for Performance Fix . We will have the ids before reaching this point
316: So we dont need to resolve bill_seq_id using UUs
317:
318: stmt_num := 9.7;
319: UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
320: SET bill_sequence_id = (SELECT bill_sequence_id
321: FROM bom_bill_of_materials bom, mtl_system_items_vl mvll
322: WHERE mvll.concatenated_segments = BBMI.item_number
323: AND mvll.organization_id = BBMI.organization_id
338:
339:
340: stmt_num := 9.8;
341: /* Update structure type name to the internal name from the display name */
342: UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
343: SET structure_type_name = (SELECT structure_type_name
344: FROM BOM_STRUCTURE_TYPES_VL bstv
345: WHERE decode(BBMI.structure_type_name, null, to_char(bstv.structure_type_id), bstv.display_name)
346: = decode(BBMI.structure_type_name, null, to_char(BBMI.structure_type_id), BBMI.structure_type_name))
395: NVL(PROGRAM_APPLICATION_ID, prog_appid),
396: NVL(PROGRAM_ID, prog_id),
397: NVL(PROGRAM_UPDATE_DATE, sysdate),
398: NVL(BATCH_ID,0) -- Replace NULL batch id with 0 - table level default value for set_process_id
399: FROM BOM_BILL_OF_MTLS_INTERFACE
400: WHERE process_flag = 1
401: AND transaction_type = G_Create
402: AND (all_org = 1
403: OR
446: BBMI.transaction_id,
447: MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
448: Null,
449: null,
450: 'BOM_BILL_OF_MTLS_INTERFACE',
451: decode ( BBMI.Organization_code, null, msg_name1,msg_name2),
452: decode ( BBMI.Organization_code, null, msg_text1,msg_text2),
453: NVL(LAST_UPDATE_DATE, SYSDATE),
454: NVL(LAST_UPDATED_BY, user_id),
459: NVL(PROGRAM_APPLICATION_ID, prog_appid),
460: NVL(PROGRAM_ID, prog_id),
461: NVL(PROGRAM_UPDATE_DATE, sysdate)
462:
463: from BOM_BILL_OF_MTLS_INTERFACE BBMI
464: where (organization_code is null or item_number is null)
465: and transaction_id is not null
466: and process_flag =1
467: and (all_org = 1 OR (all_org = 2 AND organization_id = org_id))
471: or ( p_batch_id = bbmi.batch_id )
472: );
473:
474:
475: Update BOM_BILL_OF_MTLS_INTERFACE BBMI
476: set process_flag = 3
477: where (item_number is null or Organization_code is null)
478: and transaction_id is not null
479: and process_flag =1
2219: RETURN l_return_status;
2220: END IF;
2221:
2222: /* Set PK3_value if the value for revision exists */
2223: UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
2224: SET pk3_value = (SELECT mrb.revision_id
2225: FROM mtl_item_revisions_b mrb
2226: WHERE mrb.inventory_item_id = BBMI.Assembly_item_id
2227: AND mrb.organization_id = BBMI.organization_id