Chandra Sekhar Kadali - Oracle Apps Knowledge Sharing: Mar 4, 2010

Thursday, March 04, 2010

Saled Orders Pegged to PO

SELECT /*+ NO_MERGE(MED) */

med.planner_code F01

,SUBSTR(med.category_name,INSTR(med.category_name,'.',1,2)+1,INSTR(med.category_name,'.')) F02

,SUBSTR(med.category_name,INSTR(med.category_name,'.',1,3)+1,INSTR(med.category_name,'.')) F03

,vendor.vend_name F04

,calc_date.organization_code F05

,SUBSTR(med.order_number,1,7) F06 ----PO Number

,pha.comments F07

,DECODE(ms.firm_planned_type,1,'Y','N') F08

,ms.need_by_date F09

,calc_date.calendar_date F10

,med.item_segments F11

,med.item_description F12

,med.quantity F13

,calc_date.full_lead_time F14

,SUBSTR(med.end_order_number,1,INSTR(med.end_order_number,'.')-1)

' '

SUBSTR(med.end_order_number,INSTR(med.end_order_number,'(')) f15,---Sales Order Number

party.customer f16

,party.NAME f17

,TRUNC(med.dmd_satisfied_date) f18

,party.TimeSlot f19

,party.PaidFlag f20

,party.SHIPPING_METHOD_CODE f21

FROM msc_exception_details_v med

,msc_supplies ms

,msc_plans mp

,PO_HEADERS_ALL pha

,msc_demands md

,( SELECT /*+ INDEX(TP MSC_TRADING_PARTNERS_U2) */ tp.organization_code, tp.partner_name, msi.plan_id, msi.inventory_item_id, msi.full_lead_time, msi.postprocessing_lead_time, msi.organization_id, c2.CALENDAR_DATE

FROM MSC_CALENDAR_DATES C,

MSC_CALENDAR_DATES C2,

MSC_TRADING_PARTNERS TP,

msc_system_items msi

WHERE TP.SR_INSTANCE_ID = 1

AND TP.PARTNER_TYPE = 3

AND C.CALENDAR_CODE = TP.CALENDAR_CODE

AND C.EXCEPTION_SET_ID = TP.CALENDAR_EXCEPTION_SET_ID

AND C.SR_INSTANCE_ID = TP.SR_INSTANCE_ID

AND C.CALENDAR_DATE = TRUNC(SYSDATE)

AND c2.exception_set_id = c.exception_set_id

AND c2.calendar_code = c.calendar_code

AND c2.seq_num = c.prior_seq_num + NVL(msi.full_lead_time,0)

AND msi.organization_id = tp.sr_tp_id) calc_date,

(SELECT b.partner_id, c.partner_site_id, b.partner_name

' - '

c.tp_site_code vend_name

FROM msc_trading_partners b, msc_trading_partner_sites c

WHERE b.partner_id = c.partner_id

AND b.partner_type = 1

) vendor,

(SELECT partyname.last_name

' '

partyname.first_name

' ('

phone

')' customer,

oel.line_id, HAO.NAME , OEL.ATTRIBUTE3 TimeSlot,

nvl2(OEL.ATTRIBUTE7,'Y','N') PaidFlag, OEL.SHIPPING_METHOD_CODE

FROM OE_ORDER_LINES_ALL OEL, HR_ALL_ORGANIZATION_UNITS HAO,

(SELECT shp_site_use.site_use_id invoice_to_org_id

,shipaddr.address1

,shipaddr.address2

,shipaddr.address3

,shipaddr.address4

,shipaddr.city

,shipaddr.province

,shipaddr.postal_code

,shp_party_site.party_id

,shipaddr.country

,DECODE( hp.person_last_name, NULL

,hp.party_name

,hp.person_last_name) last_name

,DECODE( hp.person_pre_name_adjunct, NULL

,DECODE ( hp.person_first_name, NULL

,''

,hp.person_first_name)

,lv.meaning

' '

hp.person_first_name ) first_name

,DECODE( cp.phone_area_code,NULL

,''

,cp.phone_area_code

'-')

cp.phone_number

DECODE( cp.phone_extension,NULL

,''

,' x'

cp.phone_extension) phone

FROM hz_locations shipaddr

, hz_party_sites shp_party_site

, hz_cust_acct_sites_all shp_acct_site

, hz_cust_site_uses_all shp_site_use

, hz_parties hp

, fnd_lookup_values_vl lv

, hz_contact_points cp

WHERE shp_site_use.site_use_code = 'BILL_TO'

AND shp_site_use.cust_acct_site_id = shp_acct_site.cust_acct_site_id

AND shp_acct_site.party_site_id = shp_party_site.party_site_id

AND shipaddr.location_id = shp_party_site.location_id

AND shp_party_site.party_id = hp.party_id

AND lv.lookup_type(+) = 'CONTACT_TITLE'

AND lv.lookup_code(+) = hp.person_pre_name_adjunct

AND cp.owner_table_name(+) = 'HZ_PARTIES'

AND cp.owner_table_id(+) = shp_party_site.party_id

AND cp.status(+) = 'A'

AND cp.primary_flag(+) = 'Y'

AND cp.contact_point_type(+) = 'PHONE') partyname

WHERE partyname.invoice_to_org_id = oel.invoice_to_org_id

AND HAO.ORGANIZATION_ID = OEL.SHIP_FROM_ORG_ID

and oel.line_id in(6851629,6851624,6851627)) party

WHERE med.plan_id = mp.plan_id

AND mp.compile_designator = 'HBC_DSO'

AND med.category_set_id = 5

AND med.exception_type= 15

AND med.plan_id = calc_date.plan_id

AND med.end_order_number LIKE '%COFI Standard Sales Order%'

AND med.order_type_id = 1

AND med.organization_id = calc_date.organization_id

AND med.inventory_item_id = calc_date.inventory_item_id

AND ms.plan_id = med.plan_id

AND ms.sr_instance_id = med.sr_instance_id

AND ms.transaction_id = med.transaction_id

---AND ms.need_by_date > calc_date.calendar_date

AND ms.supplier_id = vendor.partner_id

AND ms.supplier_site_id = vendor.partner_site_id

AND md.plan_id = med.plan_id

AND md.sr_instance_id = med.sr_instance_id

AND md.inventory_item_id = med.inventory_item_id

AND md.demand_id = med.demand_id

AND party.LINE_ID = md.sales_order_line_id

AND SUBSTR(med.order_number,1,7) = pha.segment1

--ORDER BY med.planner_code, DECODE(ms.firm_planned_type,1,'Y','N') DESC,

---vendor.vend_name,calc_date.organization_code,med.order_number,med.item_segments;

Safety Stock Pegging

select *

from apps.ecx_doclogs dl

,ecx.ecx_outbound_logs ol

,apps.ecx_oxta_logmsg lm

where trunc(ol.time_stamp) between trunc(sysdate-1) and trunc(sysdate)

and dl.direction = 'OUT'

and lm.sender_message_id = dl.msgid

and ol.document_number in(select distinct wda.delivery_id from wsh_delivery_details wdd,

wsh_delivery_assignments wda

where wdd.organization_id=392

and wdd.subinventory='Stage'

and wdd.released_status='Y'

and trunc(wdd.last_update_date)=trunc(sysdate)

and wdd.delivery_detail_id=wda.delivery_detail_id)-- po_header_id

and ol.out_msgid = dl.msgid

and dl.transaction_subtype = 'OE_PICK_O'

--and lm.exception_text not like 'Response: HTTP/1.1 200 OK%'

order by dl.time_stamp desc

Picks related to Organization

PICKS IN COFI NOT IN WM WITH SHIP METHOD

select ------------PICKS IN COFI NOT IN WM WITH SHIP METHOD

hou.name DC,

wda.delivery_id PICK_TICKET,

oh.order_number,

ol.line_number LINE_NUMBER,

ol.ordered_item,

ol.shipping_method_code,

wdd.requested_quantity RELEASED_QUANTITY,

ol.schedule_ship_date,

decode(wdd.released_status,'Y','PICKED/STAGED') Released_status,

wdd.tp_attribute14 REQUEST_ID

from

hr_organization_units hou,

wsh_delivery_details wdd,

wsh_delivery_assignments wda,

oe_order_headers_all oh,

oe_order_lines_all ol

where

hou.type='DC'

and wdd.delivery_detail_id=wda.delivery_detail_id

and wdd.source_header_id=oh.header_id

and wdd.source_line_id=ol.line_id

and ol.ship_from_org_id=hou.organization_id

and oh.header_id=ol.header_id

and wdd.released_status in('Y')

--and wda.delivery_id is not null

and ol.schedule_ship_date is not null

and not exists (select dl.msgid

from apps.ecx_doclogs dl

,ecx.ecx_outbound_logs ol

,apps.ecx_oxta_logmsg lm

where dl.direction = 'OUT'

and lm.sender_message_id = dl.msgid

and ol.document_number = wda.delivery_id -- po_header_id

and ol.out_msgid = dl.msgid

and dl.transaction_subtype = 'OE_PICK_O')

Picked Items Not In WM

select

hou.name DC,

wda.delivery_id PICK_TICKET,

oh.order_number,

ol.ordered_item,

wdd.requested_quantity RELEASED_QUANTITY,

ol.schedule_ship_date,

decode(wdd.released_status,'B','BACKORDERED','Y','PICKED/STAGED') Released_status

from

hr_organization_units hou,

wsh_delivery_details wdd,

wsh_delivery_assignments wda,

oe_order_headers_all oh,

oe_order_lines_all ol

where

hou.type='DC'

and wdd.delivery_detail_id=wda.delivery_detail_id

and wdd.source_header_id=oh.header_id

and wdd.source_line_id=ol.line_id

and ol.ship_from_org_id=hou.organization_id

and oh.header_id=ol.header_id

and wdd.released_status in('Y','B')

--and wda.delivery_id is not null

and ol.schedule_ship_date is not null

and not exists (select dl.msgid

from apps.ecx_doclogs dl

,ecx.ecx_outbound_logs ol

,apps.ecx_oxta_logmsg lm

where dl.direction = 'OUT'

and lm.sender_message_id = dl.msgid

and ol.document_number = wda.delivery_id -- po_header_id

and ol.out_msgid = dl.msgid

and dl.transaction_subtype = 'OE_PICK_O')

Modifiers At Line Level

SELECT qlh.comments "Modifier Name"
      ,qms.product_attr_value "SKU"
      ,qms.list_line_no "Modifier Line No"
      ,qms.start_date_active
      ,qms.end_date_active
      ,qms.arithmetic_operator_type "Application Method"
      ,qms.operand "Value"
      ,qms.product_precedence
      ,qms.incompatibility_grp
      ,qms.pricing_group_sequence "Bucket"
FROM qp_modifier_summary_v qms
      ,qp_list_headers_b qlh
WHERE qlh.list_header_id = qms.list_header_id
   AND EXISTS(SELECT 1
                FROM mtl_system_items_b a
               WHERE a.organization_id = 22 AND TO_CHAR(a.inventory_item_id) = qms.product_attr_val AND a.segment1 IN('61579702') )

Modifiers At Item Catalog Level

SELECT DISTINCT                                                                                            -- qpa.list_header_id "Modifier Header ID",
                qpa.list_line_id "Modifier Line Number"
               ,qlh.comments "Modifier Name (Description)"
               ,qll.start_date_active "Modifier Start Date"
               ,qll.end_date_active "Modifier End Date"
               ,b.segment1 "SKU"
               ,b.inventory_item_id "Inventory Item ID"
               ,qll.arithmetic_operator_type "Application Method"
               ,qll.operand "Value"
               ,qll.product_precedence "Precedence"
               ,qll.incompatibility_grp "Incompatibility Group"
               ,qll.pricing_group_sequence "Bucket"
           FROM qp_pricing_attributes qpa
               ,qp_qualifiers_v qq
               ,qp_list_headers_b qlh
               ,qp_modifier_summary_v qll
               ,mtl_item_catalog_groups a
               ,mtl_system_items_b b
               ,mtl_descr_element_values c

--qp_qualifiers_v q

WHERE b.item_catalog_group_id = a.item_catalog_group_id

AND b.inventory_item_id = c.inventory_item_id

AND b.organization_id =

(SELECT UNIQUE master_organization_id

FROM mtl_parameters)

AND c.element_sequence IN ('20')

AND qq.list_header_id = qpa.list_header_id

AND qq.list_line_id = qpa.list_line_id

AND qq.qualifier_attribute = 'QUALIFIER_ATTRIBUTE35'

AND qq.qualifier_attr_value =a.segment1'-'c.element_value

AND qpa.product_attribute = 'PRICING_ATTRIBUTE3'

AND qlh.list_header_id = qq.list_header_id

AND qpa.list_header_id = qll.list_header_id

AND qpa.list_line_id = qll.list_line_id

AND qll.list_header_id = qlh.list_header_id

AND qlh.active_flag = 'Y'

---AND SYSDATE BETWEEN qll.start_date_active AND qll.end_date_active

AND b.segment1 = '47563473'

To Get Active Price list,Modifiers,

SELECT distinct qlh.comments "Modifier Name"

,qqv.rule_name "Qualifier Group"

,ou.name "Store Id"

--,qlhv.name "Price list name"

,qms.product_attr_value "SKU"

,qms.list_line_no "Modifier Line No"

,qms.start_date_active "Start Date"

,qms.end_date_active "End Date"

,qms.arithmetic_operator_type "Application Method"

,qms.operand "Value"

,qms.product_precedence "Precedence"

,qms.incompatibility_grp "Incompatibility Group"

,qms.pricing_group_sequence "Bucket"

FROM

qp_modifier_summary_v qms

, qp_list_headers_b qlh

,qp_list_headers_tl qlt

,qp_qualifiers_v qqv

,mtl_system_items_b msi

,hr_all_organization_units ou

--,qp_list_headers_v qlhv

WHERE

qlh.list_header_id = qms.list_header_id

--and qms.list_header_id=qlhv.list_header_id

and qlh.list_header_id =qqv.list_header_id

and to_char(msi.inventory_item_id)=qms.product_attr_val

AND ou.organization_id = msi.organization_id

and to_char(ou.organization_id)= qqv.qualifier_attr_value

and sysdate between qms.start_date_active and qms.end_date_active

and qlt.LIST_HEADER_ID=qlh.LIST_HEADER_ID

AND exists

(select 1

from mtl_system_items_b a

where a.organization_id=22

and to_char(a.inventory_item_id)=qms.product_attr_val

and a.segment1 in('61054243'))

Active Price list query:

SELECT msi.segment1 "Sku",

msi.description "Sku Description",

qph.name "Price list name",

qpl.START_DATE_ACTIVE "Start Date",

qpl.END_DATE_ACTIVE "End date"

---qph.list_header_id

FROM

qp_list_headers_v qph,

qp.qp_list_lines qpl,

qp_pricing_attributes qpa,

mtl_system_items_b msi

WHERE

---source_lang = 'US'

qph.list_header_id = qpl.list_header_id

AND qpa.list_line_id = qpl.list_line_id

AND qpa.list_header_id = qph.list_header_id

AND qpa.product_attribute_context = 'ITEM'

AND product_attribute = 'PRICING_ATTRIBUTE1'

AND msi.organization_id = 22

AND msi.inventory_item_id = qpa.product_attr_value

--AND qpl.end_date_active IS NULL

--AND qph.LANGUAGE = 'US'

and ((sysdate between qpl.START_DATE_ACTIVE

and qpl.END_DATE_ACTIVE)

or (qpl.START_DATE_ACTIVE < sysdate

and qpl.END_DATE_ACTIVE is null ))

AND msi.segment1 in ('61054243')

Active Modifiers:

SELECT distinct qlh.comments "Modifier Name"

,qms.start_date_active "Start Date"

,qms.end_date_active "End Date"

,qlh.active_flag

,qlh.automatic_flag

FROM

qp_modifier_summary_v qms,

qp_list_headers_b qlh

WHERE

qlh.list_header_id = qms.list_header_id

and sysdate between qms.start_date_active and qms.end_date_active

and trunc(qms.end_date_active) <'12-JAN-2009'

To Get Modifier Details at Price LIst Level

SELECT DISTINCT qpa.list_header_id "Modifier Header ID",

qlh.COMMENTS "Modifier Name (Description)",

qpa.list_line_id "Modifier Line ID",

qll.start_date_active "Modifier start date",

qll.end_date_active "Modifier end date",

qq.qualifier_attr_value "Price List ID",

qllv.product_attr_value "Inventory Item ID",

msi.segment1 "SKU",

qll.arithmetic_operator_type "Application Method",

qll.operand "Value",

qll.product_precedence "Precedence",

qll.incompatibility_grp "Incompatibility Group",

qll.pricing_group_sequence "Bucket"

FROM qp_pricing_attributes qpa,

mtl_system_items_b msi,

qp_qualifiers_v qq,

qp_list_headers_b qlh,

qp_list_lines_v qllv,

qp_modifier_summary_v qll

WHERE qpa.product_attribute = 'PRICING_ATTRIBUTE3'

AND qq.qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'

AND qllv.product_attribute = 'PRICING_ATTRIBUTE1'

AND qllv.product_attr_value = msi.inventory_item_id

AND qllv.list_header_id = qq.qualifier_attr_value

AND qlh.list_header_id = qq.list_header_id

AND qpa.list_header_id = qll.list_header_id

AND qpa.list_line_id = qll.list_line_id

AND qll.list_header_id = qlh.list_header_id

AND qlh.active_flag = 'Y'

AND msi.segment1 = '61579702'

---AND SYSDATE BETWEEN qll.start_date_active AND qll.end_date_active

---AND TRUNC (NVL (qllv.end_date_active, SYSDATE)) <= TRUNC (SYSDATE)

To Get MATERIAL Transaction Details

select mp1.organization_code "Org" ---Material Transactions by SKU and ORG

,mp1.organization_name

,msi.segment1 "SKU"

,mmt.subinventory_code "Subinventtory"

,mmt.transfer_subinventory "Transfer Subinventory"

,mp2.organization_code "Transfer Org"

,mmt.transaction_date

,mmt.transaction_quantity

,mtst.transaction_source_type_name "Source Type"

,mgd.segment1 "Source",mmt.transaction_type_id

,mtt.transaction_type_name "Transaction Type"

,ml.meaning "Transaction Action"

,mmt.shipment_number

,fu.user_name

,(select order_number from oe_order_headers_all oeh,oe_order_lines_all oel

where oeh.header_id=oel.header_id and oel.line_id=mmt.trx_source_line_id) order_number

,(select poh.segment1 from po_headers_all poh

where poh.po_header_id=mmt.transaction_source_id ) po_number

,mmt.transaction_id

,mmt.source_code

from mtl_material_transactions mmt

-- ,mtl_parameters mp1

,org_organization_definitions mp1

,hr_organization_units hou

,mtl_system_items_b msi

,mtl_parameters mp2

,mtl_transaction_types mtt

,mtl_txn_source_types mtst

,mfg_lookups ml

,fnd_user fu

,mtl_generic_dispositions mgd

where mmt.organization_id = mp1.organization_id

and hou.organization_id = mp1.organization_id

--and mmt.transaction_type_id = 8 --Physical Inv Adjust

-- and mmt.transaction_type_id in (2,31,41) --31 - Alias Issue, 41 - Alias Receipt, 2 - Subinv Transfer

--and mmt.organization_id = 173

-- and hou.type in ('CC','STORE')

and mmt.inventory_item_id = msi.inventory_item_id

and msi.organization_id = 22

and mp1.organization_code = 'EBT' ---organization(DC)

---and msi.segment1 = '47277371'

and mmt.transfer_organization_id = mp2.organization_id(+)

and mtt.transaction_type_id = mmt.transaction_type_id

and mtst.transaction_source_type_id = mmt.TRANSACTION_SOURCE_TYPE_ID

and ml.LOOKUP_TYPE ='MTL_TRANSACTION_ACTION'

and ml.LOOKUP_CODE = mmt.transaction_action_id

and fu.user_id(+) = mmt.created_by

and mgd.disposition_id(+) = mmt.transaction_source_id

and mgd.organization_id(+) = mmt.organization_id

--and mgd.segment1='On-Hand Adjustment'

and mmt.TRANSACTION_DATE BETWEEN to_date('02-03-2009 00:00:00','DD-MM-YYYY HH24:MI:SS') ---from date

and to_date('03-03-2009 23:59:59','DD-MM-YYYY HH24:MI:SS') ---to date

---and msi.segment1='64709397'

-- and ( mmt.TRANSACTION_ACTION_ID NOT IN (24,30) )

--and fu.user_name = 'ESPCOFI' /** Physical Inv job ran under ESPCOFI

order by mp1.organization_code, msi.segment1, mmt.subinventory_code

To Get ISO pegged to CSO

SELECT ------------PICKS IN COFI NOT IN WM WITH SHIP METHOD

hou.NAME DC,

wda.delivery_id PICK_TICKET,

SUBSTR(md.order_number,1,(INSTR(md.order_number,'.',1,1)-1)) "Orders not in WM",

DECODE(LENGTH(SUBSTR(mfsd.end_disposition,1,(INSTR(mfsd.end_disposition,'.',1,1)-1))),9,null,SUBSTR(mfsd.end_disposition,1,(INSTR(mfsd.end_disposition,'.',1,1)-1))) "Pegging info(CSO)"

,ol.line_number LINE_NUMBER,

ol.ordered_item,

ol.shipping_method_code,

wdd.requested_quantity RELEASED_QUANTITY,

ol.schedule_ship_date,

DECODE(wdd.released_status,'Y','PICKED/STAGED') Released_status,

wdd.tp_attribute14 REQUEST_ID

--wdd.ship_to_location_id

FROM

hr_organization_units hou,

wsh_delivery_details wdd,

wsh_delivery_assignments wda,

oe_order_headers_all oh,

oe_order_lines_all ol,

msc_flp_supply_demand_v mfsd,

msc_demands md,

oe_order_lines_all ol2

WHERE hou.TYPE='DC'

AND wdd.delivery_detail_id=wda.delivery_detail_id

AND wdd.source_header_id=oh.header_id

AND wdd.source_line_id=ol.line_id

AND ol.ship_from_org_id=hou.organization_id

AND oh.header_id=ol.header_id

AND wdd.released_status IN('Y')

--and wda.delivery_id is not null

AND ol.schedule_ship_date IS NOT NULL

AND NOT EXISTS (SELECT dl.msgid

FROM apps.ecx_doclogs dl

,ecx.ecx_outbound_logs ol

,apps.ecx_oxta_logmsg lm

WHERE dl.direction = 'OUT'

AND lm.sender_message_id = dl.msgid

AND ol.document_number = wda.delivery_id -- po_header_id

AND ol.out_msgid = dl.msgid

AND dl.transaction_subtype = 'OE_PICK_O')

AND mfsd.demand_id=md.demand_id

--AND decode(substr(mfsd.end_disposition,14,14),'Standard Sales'

AND SUBSTR(md.order_number,15,15)=' Internal Order'

AND ol2.line_id=md.sales_order_line_id

AND mfsd.origination_name='Sales Orders'

--and substr(b.order_number,1,(instr(b.order_number,'.',1,1)-1))= &internal_order_number

AND mfsd.plan_id=1

AND mfsd.end_disposition IS NOT NULL

AND oh.order_number=SUBSTR(md.order_number,1,9)

AND wdd.source_line_id=md.sales_order_line_id

--and oh.order_number in ('100077315','100077314')

AND oh.order_type_id=1009

UNION

SELECT

hou.NAME DC,

wda.delivery_id PICK_TICKET,

TO_CHAR(oh.order_number) "Orders not in WM"

,null "Pegging info(CSO)"

,ol.line_number LINE_NUMBER,

ol.ordered_item,

ol.shipping_method_code,

wdd.requested_quantity RELEASED_QUANTITY,

ol.schedule_ship_date,

DECODE(wdd.released_status,'Y','PICKED/STAGED') Released_status,

wdd.tp_attribute14 REQUEST_ID

FROM

hr_organization_units hou,

wsh_delivery_details wdd,

wsh_delivery_assignments wda,

oe_order_headers_all oh,

oe_order_lines_all ol

WHERE

hou.TYPE='DC'

AND wdd.delivery_detail_id=wda.delivery_detail_id

AND wdd.source_header_id=oh.header_id

AND wdd.source_line_id=ol.line_id

AND ol.ship_from_org_id=hou.organization_id

AND oh.header_id=ol.header_id

--and oh.order_number in ('100077315','100077314')

AND oh.order_type_id=1008

AND wdd.released_status IN('Y')

--and wda.delivery_id is not null

AND ol.schedule_ship_date IS NOT NULL

AND NOT EXISTS (SELECT dl.msgid

FROM apps.ecx_doclogs dl

,ecx.ecx_outbound_logs ol

,apps.ecx_oxta_logmsg lm

WHERE dl.direction = 'OUT'

AND lm.sender_message_id = dl.msgid

AND ol.document_number = wda.delivery_id -- po_header_id

AND ol.out_msgid = dl.msgid

AND dl.transaction_subtype = 'OE_PICK_O')

UNION

SELECT

hou.NAME DC,

wda.delivery_id PICK_TICKET,

TO_CHAR(oh.order_number) "Orders not in WM"

,NULL "Pegging info(CSO)"

,ol.line_number LINE_NUMBER,

ol.ordered_item,

ol.shipping_method_code,

wdd.requested_quantity RELEASED_QUANTITY,

ol.schedule_ship_date,

DECODE(wdd.released_status,'Y','PICKED/STAGED') Released_status,

wdd.tp_attribute14 REQUEST_ID

FROM

hr_organization_units hou,

wsh_delivery_details wdd,

wsh_delivery_assignments wda,

oe_order_headers_all oh,

oe_order_lines_all ol

WHERE

hou.TYPE='DC'

AND wdd.delivery_detail_id=wda.delivery_detail_id

AND wdd.source_header_id=oh.header_id

AND wdd.source_line_id=ol.line_id

AND ol.ship_from_org_id=hou.organization_id

AND oh.header_id=ol.header_id

--and oh.order_number in ('100077315','100077314')

AND wdd.released_status IN('Y')

--and wda.delivery_id is not null

AND ol.schedule_ship_date IS NOT NULL

AND NOT EXISTS (SELECT dl.msgid

FROM apps.ecx_doclogs dl

,ecx.ecx_outbound_logs ol

,apps.ecx_oxta_logmsg lm

WHERE dl.direction = 'OUT'

AND lm.sender_message_id = dl.msgid

AND ol.document_number = wda.delivery_id -- po_header_id

AND ol.out_msgid = dl.msgid

AND dl.transaction_subtype = 'OE_PICK_O')

AND NOT EXISTS(SELECT md.sales_order_line_id FROM

msc_demands md WHERE

ol.line_id=md.sales_order_line_id

To Get Inventory transaction Id from Order Line ID

SELECT transaction_id

FROM mtl_material_transactions

WHERE trx_source_line_id = &order_line_id

AND transaction_type_id = 33

To identify redilvery lines

select -- UNDELIVERED LINE CANCELLED REDELIVERY LINE NOT CANCELLED

h.ORDER_NUMBER "Order Number",

Rl.ORDERED_ITEM "Sku Number",

Rl.LINE_NUMBER "Closed Redelivery Line#",

a.LINE_NUMBER "Cancelled Outbound Line#",

decode(ol.SUBINVENTORY,'','Available',ol.subinventory) "Orig Sub",

ol.LINE_NUMBER "Orig Line#",

mp.ORGANIZATION_CODE "Shipping Org",

mp2.ORGANIZATION_CODE "Selling Store",

trunc(oe.HIST_CREATION_DATE) "Cancellation Date",

SUBSTR(fnd.USER_NAME,1,15) "Cancelled by (ID)",

SUBSTR(fnd.DESCRIPTION,1,25) "Cancelled by (Name)"

FROM oe_order_headers_all h,

oe_order_lines_all Rl,

oe_order_lines_all a,

oe_order_lines_all ol,

oe_ORDER_LINES_AUDIT_V oe,

fnd_user fnd,

inv.mtl_parameters mp,

inv.mtl_parameters mp2

WHERE 1 = 1

and h.ATTRIBUTE1 = mp2.ORGANIZATION_ID

and h.HEADER_ID = oe.HEADER_ID

and h.ORDER_NUMBER = oe.ORDER_NUMBER

and a.LINE_ID = oe.LINE_ID

and fnd.user_id = oe.USER_ID

and a.SHIP_FROM_ORG_ID = mp.ORGANIZATION_ID

/*the cancelled outbound redelivery line*/

and h.HEADER_ID = a.HEADER_ID

and a.SUBINVENTORY = 'Redelivery'

and a.GLOBAL_ATTRIBUTE4 = 'POND'

and a.FLOW_STATUS_CODE = 'CANCELLED'

and a.CANCELLED_FLAG = 'Y'

and a.Item_type_Code = 'STANDARD'

and a.LINE_CATEGORY_CODE = 'ORDER'

and oe.HIST_TYPE_CODE = 'CANCELLATION'

/*the closed undelivered/redelivery line*/

and h.HEADER_ID = Rl.HEADER_ID

and Rl.ORIG_SYS_LINE_REF = a.ORIG_SYS_LINE_REF

and Rl.line_id = a.Global_attribute3

and Rl.ORDERED_ITEM = a.ORDERED_ITEM

and Rl.SUBINVENTORY = 'Redelivery'

and Rl.GLOBAL_ATTRIBUTE4 = 'POND'

and Rl.FLOW_STATUS_CODE = 'CLOSED'

and Rl.CANCELLED_FLAG = 'N'

and Rl.Item_type_Code = 'STANDARD'

and Rl.LINE_CATEGORY_CODE = 'RETURN'

-- find original closed order line

and h.HEADER_ID = ol.HEADER_ID

and ol.LINE_ID = Rl.REFERENCE_LINE_ID

and ol.LINE_ID = Rl.RETURN_ATTRIBUTE2

and ol.ORDERED_ITEM = a.ORDERED_ITEM

and ol.FLOW_STATUS_CODE = 'CLOSED'

and ol.CANCELLED_FLAG = 'N'

and ol.LINE_CATEGORY_CODE = 'ORDER'

/*enter the date range*/

and trunc(oe.HIST_CREATION_DATE) BETWEEN to_date('16-OCT-2008','dd-mon-yyyy') and to_date('22-Oct-2008','dd-mon-yyyy')

order by h.ORDER_NUMBER, a.LINE_ID

To get order lines cancelled before delivery.sql

select

h.ORDER_NUMBER "Order Number",

oel.ORDERED_ITEM "Sku Number",

oel.LINE_NUMBER "Cancelled Line Number",

decode(oel.SUBINVENTORY,'','Available',oel.subinventory) "Orig Sub",

oel.LINE_NUMBER "Orig Line#",

mp.ORGANIZATION_CODE "Shipping Org",

mp2.ORGANIZATION_CODE "Selling Store",

trunc(oea.HIST_CREATION_DATE) "Cancellation Date",

---trunc(oel.schedule_ship_date) "Original ship date",

SUBSTR(fnd.USER_NAME,1,15) "Cancelled by (ID)",

SUBSTR(fnd.DESCRIPTION,1,25) "Cancelled by (Name)"

FROM oe_order_headers_all h,

oe_order_lines_all oel,

oe_ORDER_LINES_AUDIT_V oea,

fnd_user fnd,

inv.mtl_parameters mp,

inv.mtl_parameters mp2

WHERE 1 = 1

and h.ATTRIBUTE1 = mp2.ORGANIZATION_ID

and h.HEADER_ID = oel.HEADER_ID

and h.ORDER_NUMBER = oea.ORDER_NUMBER

and oel.LINE_ID = oea.LINE_ID

and fnd.user_id = oea.USER_ID

and oel.SHIP_FROM_ORG_ID = mp.ORGANIZATION_ID

and h.HEADER_ID = oel.HEADER_ID

and oel.FLOW_STATUS_CODE = 'CANCELLED'

and oel.CANCELLED_FLAG = 'Y'

and oel.Item_type_Code = 'STANDARD'

and oel.LINE_CATEGORY_CODE = 'ORDER'

and oea.HIST_TYPE_CODE = 'CANCELLATION'

and oel.shipping_method_code='000001_Home Deliv_T_STORESTOCK'

and (oel.schedule_ship_date is null

or trunc(oea.HIST_CREATION_DATE) < trunc(oel.schedule_ship_date))

and h.order_number in(1132427,1110368,1155737,1016372,1016525,1111516,1103495,1104359)

To Get Installbase Details

t oeh.order_number "Order Number"

,oeh.ordered_date "Order Date"

--- ,hzp.party_number "Party"

--- ,hzp.party_name "Party Name"

,oel.line_number "Order Line"

,msi.segment1 "Item Sku"

,msi.description "Item Description"

,msi.inventory_item_status_code "Item Type"

--,msi.item_type "Item Type"

,oel.ordered_quantity "Ordered Qty"

,oel.fulfilled_quantity "Fulfilled Qty"

--,oel.cancelled_quantity "Cancelled Qty"

--,oeh.shipping_method_code "Header Shipping Method"

--,oel.shipping_method_code "Line Shipping Method"

,oel.fulfillment_date "Fulfillment Date"

,hao.name "Store"

,oel.unit_selling_price "Unit Selling Price"

--- ,jre.source_name "Sales Associate"

--,oel.subinventory "Sub-Inventory"

from oe_order_lines_all oel

,oe_order_headers_all oeh

,hr_all_organization_units hao

,mtl_system_items_b msi

---,hz_cust_site_uses_all hzsu

---,hz_cust_acct_sites_all hzas

---,hz_party_sites hzps

---,hz_parties hzp

---,jtf_rs_resource_extns jre

where 1=1

and oeh.header_id = oel.header_id

and oel.item_type_code = 'STANDARD'

and oel.line_category_code = 'ORDER'

and oel.flow_status_code = 'CLOSED'

and oeh.order_type_id=1008

and oel.ship_from_org_id = hao.organization_id

---and oeh.shipping_method_code = '000001_Customer_P_CARRY'

and msi.inventory_item_id = oel.inventory_item_id

and msi.organization_id = 22

and msi.COMMS_NL_TRACKABLE_FLAG = 'Y'

---and hzsu.site_use_id = oel.ship_to_org_id

---and hzas.cust_acct_site_id = hzsu.cust_acct_site_id

---and hzps.party_site_id = hzas.party_site_id

---and hzp.party_id = hzps.party_id

---and jre.resource_id(+) = to_number(oeh.attribute6)

--and jre.resource_id(+) = oel.salesrep_id

and not exists (select 1

from csi.csi_item_instances cii

where ---cii.accounting_class_code = 'CUST_PROD'

---and cii.instance_status_id not in (1,5)

cii.last_oe_order_line_id = oel.line_id)

/*and not exists (select 1

from oe_order_lines_all oel2

where oel.header_id = oel2.header_id

and oel.line_id = oel2.reference_line_id

and oel2.item_type_code = 'STANDARD'

and oel2.line_category_code = 'RETURN')*/

and oeh.ordered_date > '01-JAN-2008'

--and trunc(oeh.ordered_date) between '01-DEC-2006' and '31-DEC-2006'

--and oeh.order_number in ('832705','835744','876984')

--and msi.inventory_item_id = cii.inventory_item_id

--and msi.organization_id = 22

--and hp.party_id = cii.owner_party_id

To Get all the Active price list Information

Select MSI.SEGMENT1 "SKU"

,MSI.DESCRIPTION "Description"

,MSI.INVENTORY_ITEM_STATUS_CODE "Item Status"

,PLH.NAME "Price List Name"

,PLL.LIST_PRICE "Price"

,PLL.LAST_UPDATE_DATE "List Price Last

Updated"

,PLL.START_DATE_ACTIVE "Start Date"

,PLL.END_DATE_ACTIVE "End Date"

,PLL.ATTRIBUTE1 "Price Type"

,CICT.ITEM_COST "Frozen Cost"

,CICT.LAST_UPDATE_DATE "Frozen Cost Last

Updated"

From QP_PRICE_LISTS_V PLH

,QP_PRICE_LIST_LINES_V PLL

,MTL_SYSTEM_ITEMS_B MSI

----,CST_ITEM_COST_TYPE_V CICT

Where MSI.INVENTORY_ITEM_ID =

PLL.INVENTORY_ITEM_ID

and PLH.PRICE_LIST_ID = PLL.PRICE_LIST_ID

and MSI.INVENTORY_ITEM_ID =

CICT.INVENTORY_ITEM_ID

and MSI.ORGANIZATION_ID = 22

-----and CICT.ORGANIZATION_ID = 22

----and CICT.COST_TYPE = 'Frozen'

and MSI.INVENTORY_ITEM_FLAG = 'Y'

and MSI.ITEM_TYPE <> 'VM'

and MSI.INVENTORY_ITEM_STATUS_CODE <>

'Suppressed'

and PLL.END_DATE_ACTIVE is null

and (PLL.START_DATE_ACTIVE is null or

trunc(PLL.start_date_active) <=

trunc(SYSDATE))

and (PLL.END_DATE_ACTIVE is null or

trunc(PLL.end_date_active) >= trunc(SYSDATE))

----and CICT.ITEM_COST <> PLL.LIST_PRICE

-----nd PLL.LIST_PRICE <> 0

----and CICT.ITEM_COST <> 0

------and (trunc(CICT.LAST_UPDATE_DATE) <

trunc(SYSDATE))

To Send Concuurent Program Output as an E-mail

CREATE OR REPLACE PROCEDURE apps.erp_send_email

(

errbuf VARCHAR2,

retode NUMBER,

p_concurrent_program_name VARCHAR2,

p_parameter1 NUMBER

)

IS

/*Variable declaration*/

fhandle UTL_FILE.file_type;

vtextout VARCHAR2 (32000);

text VARCHAR2 (32000);

v_request_id NUMBER := NULL;

v_request_status BOOLEAN;

v_phase VARCHAR2 (2000);

v_wait_status VARCHAR2 (2000);

v_dev_phase VARCHAR2 (2000);

v_dev_status VARCHAR2 (2000);

v_message VARCHAR2 (2000);

v_application_id NUMBER;

v_concurrent_program_id NUMBER;

v_conc_prog_short_name VARCHAR2 (100);

v_conc_prog_appl_short_name VARCHAR2 (100);

v_output_file_path VARCHAR2 (200);

BEGIN

fnd_file.put_line (fnd_file.output, '------------------------------------------------------' );

fnd_file.put_line (fnd_file.output, 'Conc Prog: '

p_concurrent_program_name );

fnd_file.put_line (fnd_file.output, 'Parameter 1:'

p_parameter1 );

BEGIN

/* Get Concurrent_program_id of the desired program and application_id */

SELECT concurrent_program_id, application_id

INTO v_concurrent_program_id, v_application_id

FROM fnd_concurrent_programs_tl

WHERE user_concurrent_program_name = p_concurrent_program_name;

fnd_file.put_line (fnd_file.LOG,'Conc Prog ID:'

v_concurrent_program_id );

fnd_file.put_line (fnd_file.LOG, 'Application ID: '

v_application_id );

/* Get the program’s Short name */

SELECT concurrent_program_name

INTO v_conc_prog_short_name

FROM fnd_concurrent_programs

WHERE concurrent_program_id = v_concurrent_program_id;

fnd_file.put_line (fnd_file.LOG,'Conc Prog Short Name:'

v_conc_prog_short_name );

/* Get the Application Short name */

SELECT application_short_name

INTO v_conc_prog_appl_short_name

FROM fnd_application

WHERE application_id = v_application_id;

fnd_file.put_line (fnd_file.LOG,'Application Short Name:'

v_conc_prog_appl_short_name );

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.LOG, 'Error: '

SQLERRM);

END;

/* Calling fnd_request.submit_request to submit the desired the concurrent program*/

v_request_id:= fnd_request.submit_request

(v_conc_prog_appl_short_name,

v_conc_prog_short_name,

NULL, --Description

NULL, --Time to start the program

FALSE, -- sub program

p_parameter1

);

fnd_file.put_line (fnd_file.LOG,'Concurrent Request Submitted Successfully: '

v_request_id );

COMMIT;

IF v_request_id IS NOT NULL THEN

/*Calling fnd_concurrent.wait_for_request to wait for the program to complete */

v_request_status:= fnd_concurrent.wait_for_request

(

request_id => v_request_id,

INTERVAL => 10,

max_wait => 0,

phase => v_phase,

status => v_wait_status,

dev_phase => v_dev_phase,

dev_status => v_dev_status,

MESSAGE => v_message

);

v_dev_phase := NULL;

v_dev_status := NULL;

END IF;

/* Getting the path where output file of the program is created */

SELECT outfile_name

INTO v_output_file_path

FROM fnd_concurrent_requests

WHERE request_id = v_request_id;

/* Open the output file in Read mode */

fhandle := UTL_FILE.fopen ('/cdv01/oracle/cdvappl/xxcofidata/1.0.0/outbound','o'

v_request_id

'.out', ‘r');

IF UTL_FILE.is_open (fhandle) THEN

DBMS_OUTPUT.put_line ('File read open');

ELSE

DBMS_OUTPUT.put_line ('File read not open');

END IF;

/* Get the contents of the file into variable “text”*/

LOOP

BEGIN

UTL_FILE.get_line (fhandle, vtextout);

text := text

vtextout

UTL_TCP.crlf;

EXCEPTION

WHEN NO_DATA_FOUND THEN

EXIT;

END;

END LOOP;

UTL_FILE.fclose (fhandle);

/*Calling UTL_MAIL.send_attach_varchar2 to send the output as Email attachment */

UTL_MAIL.send_attach_varchar2

(

sender => 'chandrasekhar_k@in.ibm.com',

recipients => 'pramchand506@gmail.com',

subject => 'Testmail',

MESSAGE => 'Hello',

attachment => text,

att_inline => FALSE

);

END;

/

Price List Extract

CREATE OR REPLACE PACKAGE BODY APPS.xxcofi_price_ext_pkg AS

/* -------------------------------------------------------------------------- */

/* Program Name : xxcofi_price_ext_pkg */

/* */

/* TYPE : PL/SQL Package */

/* */

/* Input Parms : */

/* */

/* Output Parms : -- */

/* */

/* Table Access : -- */

/* */

/* AUTHOR : Chandra Sekhar */

/* */

/* DATE : 12-Dec-2009 */

/* */

/* VERSION : 1.0 */

/* */

/* DESCRIPTION : This package will extract item prices and generate flatfile*/

/* */

/* */

/* */

/* CHANGE HISTORY */

/* -------------------------------------------------------------------------- */

/* DATE AUTHOR VERSION REASON */

/* -------------------------------------------------------------------------- */

/* 25/11/09 Chandra 1.0 Initial creation */

/* -------------------------------------------------------------------------- */

PROCEDURE main (errbuf OUT VARCHAR2,

retcode OUT VARCHAR2)

AS

/*****************************************************************************************/

--- Procedure to create report outbound File

/*****************************************************************************************/

report_name VARCHAR2 (70) := 'XXCOFI_Pricing_Extract';

col01e_desc VARCHAR2 (11) := 'Bay';

col02e_desc VARCHAR2 (8) := 'SKU Number';

col03e_desc VARCHAR2 (9) := 'Retail Price';

col04e_desc VARCHAR2 (6) := 'Manage Flag';

col05e_desc VARCHAR2 (6) := 'Item Status';

col06e_desc VARCHAR2 (10) := 'UOM';

col07e_desc VARCHAR2 (20) := 'Bay Price List';

v_file_dc UTL_FILE.FILE_TYPE;

buff_size NUMBER := 24576;

l_record_no NUMBER := 0;

l_top VARCHAR2 (200) := NULL;

l_date VARCHAR2 (60) := NULL;

l_mode VARCHAR2 (5) := NULL;

CURSOR out_record_price IS

SELECT

'Bay' f01

,msib.segment1 f02

,qll.list_price f03

,'Manage' f04

,msib.inventory_item_status_code f05

,msib.primary_unit_of_measure f06

,'BayPrice_List' f07

FROM

mtl_system_items_b msib,

qp_price_list_lines_v qll,

hr_all_organization_units hou

WHERE msib.inventory_item_id = qp_price_list_pvt.Get_Inventory_Item_Id(qll.price_list_line_id)

AND msib.organization_id= hou.organization_id

AND msib.organization_id = 22 -- for HBC organization

AND msib.inventory_item_status_code !='Suppressed'

AND (qll.end_date_active IS NULL OR qll.end_date_active>=SYSDATE)

AND SUBSTR (hou.NAME, 1, 1) !='0' -- Only for Bay Organizations

AND NOT exists (SELECT 1 FROM XXCOFI_DEFERRED_PLAN_V where segment1=msib.segment1)

AND msib.attribute_category<>'EFEE';

BEGIN

fnd_profile.get ('XXCOFIDATA_OUT', l_top); --outbound file top

l_date := TO_CHAR (SYSDATE, 'YYYYMMDDHH24MI');

fnd_file.put_line (fnd_file.LOG,'START RUNNING '

report_name

'_SS'

' REPORT PROGRAM.');

fnd_file.put_line (fnd_file.output,RPAD ('HUDSON''S BAY COMPANY', 50)

'DATE:'

SYSDATE);

fnd_file.put_line (fnd_file.output,RPAD ('Pricing Extract Report', 50)

'TIME:'

TO_CHAR (SYSDATE, 'HH24:MI:SS'));

fnd_file.put_line (fnd_file.LOG,'Outputing '

report_name

'_'

l_date

'TO '

l_top);

v_file_dc := UTL_FILE.FOPEN (l_top, 'pricelist.dat','w', buff_size);

-- CREATE THE REPORT HEADER RECORD FOR REPORT OUTPUT FILE

UTL_FILE.PUT_LINE (v_file_dc,

lpad(col01e_desc,11,' ')

lpad(col02e_desc,8,' ')

lpad(col03e_desc,9,' ')

lpad(col04e_desc,6,' ')

lpad(col05e_desc,6,' ')

lpad(col06e_desc,10,' ')

lpad(col07e_desc,20,' ');

-- PUT START MESSAGE IN THE LOG FILE

FOR out_rec_dc IN out_record_price

LOOP

l_record_no := l_record_no + 1;

UTL_FILE.PUT_LINE (v_file_dc,

lpad(out_rec_dc.f01,11,' ')

lpad(out_rec_dc.f02,8,' ')

lpad(out_rec_dc.f03,9,' ')

lpad(out_rec_dc.f04,6,' ')

lpad(out_rec_dc.f05,6,' ')

lpad(out_rec_dc.f06,10,' ')

lpad(out_rec_dc.f07,20,' ');

END LOOP;

fnd_file.put_line (fnd_file.LOG,

'Number of Records Processed'

' : '

l_record_no);

--------------------------------------------------------------------------------

fnd_file.put_line (fnd_file.output,

' ');

--------------------------------------------------------------------------------

-- BEGINNING OF ERROR Pricing Extract Report

--------------------------------------------------------------------------------

-- CREATE THE TRAILER RECORD FOR OUTPUT REPORT FILE

UTL_FILE.FFLUSH (v_file_dc);

UTL_FILE.FCLOSE (v_file_dc);

--******************************************************************************

errbuf := report_name

' REPORT FILE JOB COMPLETED SUCCESSFULLY';

retcode := '0';

-- Output Number Of Record Output

fnd_file.put_line

(fnd_file.LOG,

'------------------*********************************************----------------+');

fnd_file.put_line (fnd_file.LOG,

'Pricing Extract REPORT ');

fnd_file.put_line (fnd_file.LOG, ' ');

fnd_file.put_line (fnd_file.LOG, 'JOB COMPLETED SUCCESSFULLY');

-- FND_FILE.CLOSE;

EXCEPTION

WHEN UTL_FILE.INVALID_FILEHANDLE

THEN

UTL_FILE.FCLOSE (v_file_dc);

errbuf := 'INVALID FILE HANDLE';

retcode := '1';

fnd_file.put_line (fnd_file.LOG,SQLCODE

' AND ERROR is INVALID FILE HANDLE : '

SQLERRM);

fnd_file.CLOSE;

WHEN UTL_FILE.INVALID_PATH

THEN

UTL_FILE.FCLOSE (v_file_dc);

errbuf := 'INVALID PATH';

retcode := '2';

fnd_file.put_line (fnd_file.LOG,SQLCODE

' AND ERROR is INVALID PATH : '

SQLERRM);

fnd_file.CLOSE;

WHEN UTL_FILE.INVALID_MODE

THEN

UTL_FILE.FCLOSE (v_file_dc);

errbuf := 'INVALID MODE';

retcode := '3';

fnd_file.put_line (fnd_file.LOG,SQLCODE

' AND ERROR is INVALID MODE : '

SQLERRM);

fnd_file.CLOSE;

WHEN UTL_FILE.INVALID_OPERATION

THEN

UTL_FILE.FCLOSE (v_file_dc);

errbuf := 'INVALID OPERATION';

retcode := '4';

fnd_file.put_line (fnd_file.LOG,SQLCODE

' AND ERROR is INVALID OPERATION : '

SQLERRM);

fnd_file.CLOSE;

WHEN UTL_FILE.READ_ERROR

THEN

UTL_FILE.FCLOSE (v_file_dc);

errbuf := 'READ ERROR';

retcode := '5';

fnd_file.put_line (fnd_file.LOG,SQLCODE

' AND ERROR is READ ERROR : '

SQLERRM);

fnd_file.CLOSE;

WHEN UTL_FILE.WRITE_ERROR

THEN

UTL_FILE.FCLOSE (v_file_dc);

errbuf := 'WRITE ERROR';

retcode := '6';

fnd_file.put_line (fnd_file.LOG,SQLCODE

' AND ERROR is WRITE ERROR : '

SQLERRM);

fnd_file.CLOSE;

WHEN UTL_FILE.INTERNAL_ERROR

THEN

UTL_FILE.FCLOSE (v_file_dc);

errbuf := 'INTERNAL ERROR';

retcode := '7';

fnd_file.put_line (fnd_file.LOG,SQLCODE

' AND ERROR is INTERNAL ERROR : '

SQLERRM);

fnd_file.CLOSE;

WHEN NO_DATA_FOUND

THEN

UTL_FILE.FCLOSE (v_file_dc);

errbuf := 'NO DATA FOUND';

retcode := '8';

fnd_file.put_line (fnd_file.LOG,SQLCODE

' Error is NO DATA FOUND :'

SQLERRM);

fnd_file.CLOSE;

WHEN OTHERS

THEN

UTL_FILE.FCLOSE (v_file_dc);

errbuf := 'OTHERS ';

retcode := '9';

fnd_file.put_line (fnd_file.LOG,SQLCODE

' Error is OTHERS : '

SQLERRM);

fnd_file.CLOSE;

END main;

END xxcofi_price_ext_pkg;

/

Report on Merchandise Sold But Not Delivered

CREATE OR REPLACE PACKAGE apps.xxcofi_mer_sold_not_del_pkg

AS

/* ------------------------------------------------------------------------------------

Program Name XXCOFI_MER_SOLD_NOT_DELIVER_PKG

TYPE PLSQL Package

Input Parms run_date IN DATE

Output Parms

Table Access

oe_order_headers_all --select

oe_order_lines_all --select

mtl_system_items_b --select

hr_organization_units --select

hr_locations_all --select

mtl_item_categories_v --select

fnd_lookup_values --select

ar_cash_receipts_all --select

oe_ship_methods_v --select

cst_item_costs --select

cst_cost_types --select

mtl_parameters --select

jtf_rs_salesreps --select

po_vendors --select

po_approved_supplier_list --select

AUTHOR Chandra Sekhar kadali

DATE 22-Dec-2009

VERSION 1.0

DESCRIPTION This package queries all the merchandise items which are sold but not yet delivered.

It is built bases on the existing report xxcofi_rpt097

----------------------------------------------------------------------------------

DATE AUTHOR VERSION REASON

-----------------------------------------------------------------------------------

22-Dec2009 Chandu 1.0 Inital version

------------------------------------------------------------------------------------ */

PROCEDURE main (errbuf OUT VARCHAR2, retcode OUT VARCHAR2);

PROCEDURE setstandalonemode (status BOOLEAN);

PROCEDURE write_log (content IN VARCHAR2);

PROCEDURE write_error_record (errmsg IN VARCHAR2);

PROCEDURE write_detail_record (content IN VARCHAR2);

FUNCTION getonhandqty (a_org_id NUMBER,a_item_id NUMBER) RETURN NUMBER;

END xxcofi_mer_sold_not_del_pkg;

/

CREATE OR REPLACE PACKAGE BODY apps.xxcofi_mer_sold_not_del_pkg

IS

/* ------------------------------------------------------------------------------------

Program Name XXCOFI_MER_SOLD_NOT_DELIVER_PKG

TYPE PLSQL Package

Input Parms run_date IN DATE

Output Parms

Table Access

oe_order_headers_all --select

oe_order_lines_all --select

mtl_system_items_b --select

hr_organization_units --select

hr_locations_all --select

mtl_item_categories_v --select

fnd_lookup_values --select

ar_cash_receipts_all --select

oe_ship_methods_v --select

cst_item_costs --select

cst_cost_types --select

mtl_parameters --select

jtf_rs_salesreps --select

po_vendors --select

po_approved_supplier_list --select

AUTHOR Chandra Sekhar kadali

DATE 22-Dec-2009

VERSION 1.0

DESCRIPTION This package queries all the merchandise items which are sold but not yet delivered.

It is built bases on the existing report xxcofi_rpt097

----------------------------------------------------------------------------------

DATE AUTHOR VERSION REASON

-----------------------------------------------------------------------------------

22-Dec2009 Chandu 1.0 Inital version

------------------------------------------------------------------------------------ */

/* local variables declaration */

/* v_file_hd1 is a UTL File Record Type */

v_file_hdl UTL_FILE.file_type;

/* err_location is used for maintaining error log */

err_location VARCHAR2 (80);

/* standalone is used for maintaing boolean values based on standalone mode */

standalone BOOLEAN := FALSE;

/* Temporary varibale for writing Header Details */

tempstr VARCHAR2 (200);

/* This procedure is used to Set the standalone mode */

PROCEDURE setstandalonemode (status BOOLEAN)

IS

BEGIN

standalone := status;

END;

/*If the mode is not standalone then this procedure is used for writing output to an UTL File.

Otherwise it will write into an fnd output file */

PROCEDURE write_detail_record (content IN VARCHAR2)

IS

BEGIN

IF UTL_FILE.is_open (v_file_hdl)

THEN

UTL_FILE.put_line (v_file_hdl, content);

END IF;

IF NOT standalone

THEN

fnd_file.put_line (fnd_file.output, content);

END IF;

END;

/*This procedure is used for writing fnd log messages. We will write the report header details, record count

and other meaningful details. In any case if the program is running without the concurrent manager.

we will display the log file in FND log. */

PROCEDURE write_log (content IN VARCHAR2)

IS

BEGIN

IF standalone

THEN

/* If the program is running without concurrent manager, */

/* send the log message to screen. */

DBMS_OUTPUT.put_line (content);

ELSE

fnd_file.put_line (fnd_file.LOG, content);

END IF;

END;

/* This procedure is used for displaying error messages into the fnd out file. */

PROCEDURE write_error_record (errmsg IN VARCHAR2)

IS

/********************************************************************/

/* This procedure write an error message into the output file */

/* (either master or detail output file, depends on the value */

/* of the flag err_source_flag. It also writes the message */

/* the log file. */

/********************************************************************/

BEGIN

write_detail_record (' '); /* Produce blank line. */

write_detail_record (errmsg);

write_detail_record (' '); /* Produce blank line. */

/* Write the same message into the log file. */

write_log (errmsg);

END;

/*This function is used for calculating on hand quantity of a particular item in a particular organization.

We will use this value to find out the ¿in stock¿ and ¿on order¿ details. */

FUNCTION getonhandqty (a_org_id NUMBER, a_item_id NUMBER)

RETURN NUMBER

IS

l_qty NUMBER;

BEGIN

BEGIN

SELECT SUM (moq.transaction_quantity)

INTO l_qty

FROM apps.mtl_onhand_quantities moq

WHERE moq.organization_id = a_org_id

AND moq.inventory_item_id = a_item_id;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

write_log (' No data found in transaction quantity query');

WHEN OTHERS

THEN

write_log ( ' No data found in transaction quantity query'

SQLERRM

);

END;

IF l_qty IS NULL

THEN

l_qty := 0;

END IF;

RETURN l_qty;

END;

/* Main Procedure */

PROCEDURE main (errbuf OUT VARCHAR2, retcode OUT VARCHAR2)

IS

report_name VARCHAR2 (50) := 'XXCOFI_RPT097';

report_title VARCHAR2 (100) := 'COFI Merchandise Sold Not Delivered Split Report';

err_ret VARCHAR2 (255);

l_record_no NUMBER := 0;

l_top VARCHAR2 (200) := NULL;

cdate VARCHAR2 (20);

ctime VARCHAR2 (20);

tempstr VARCHAR2 (100);

/******************************************************************************/

/* This is the cursor for building the report. */

/* */

/* Remark: */

/* */

/* oh.attribute1 is the store to sell the merchandise. */

/* */

/* ol.attribute7 store the receipt_id where it is not null if the */

/* merchandise is paid, this generate an account receivable record in AR. */

/* Column attribute7 store the receipt_id to point to the record created */

/* in account receivable. */

/* */

/* POS Receipt = 0 is added in order to show records */

/* came from converted orders, those orders have attribute7 populated */

/* by a zero. This condition is achieved by changing the condition for */

/* linking with the acr_cash_receipts_all to be outer join, this will allow */

/* to show record with ol.attribute7 = 0, do not just put in the condition */

/* ol.attribute7 = 0, this will return with a lot of records. */

/* */

/* To determine whether the merchandises are delivered or picked up by the */

/* customers, the flow_status_code is used to serve this purpose. */

/* */

/* If the merchandise was picked up by the customer, the flow_status_code */

/* would be Shipped (temporary status) or Closed. The status will indicate */

/* that the item has not yet been picked up by the customer. */

CURSOR out_record_master

IS

SELECT DECODE

(DECODE

(INSTR

(TO_CHAR

(DECODE

(NVL

(apps.xxcofi_mer_sold_not_del_pkg.getonhandqty

(cic.organization_id,

cic.inventory_item_id

),

0

),

0, -1,

apps.xxcofi_mer_sold_not_del_pkg.getonhandqty

(cic.organization_id,

cic.inventory_item_id

)

)

),

'-'

),

0, DECODE (INSTR (osmv.meaning, 'Home'),

0, '1.1',

'2.1'

),

DECODE (INSTR (osmv.meaning, 'Home'),

0, '1.2',

'2.2'

)

),

1.1, 'Customer Not Pick Up (In Stock)',

2.1, 'Home Delivry (In Stock)',

3.1, 'Delivered - No delivery confirmed from Matrix',

NULL

) in_stock,

DECODE

(DECODE

(INSTR

(TO_CHAR

(DECODE

(NVL

(apps.xxcofi_mer_sold_not_del_pkg.getonhandqty

(cic.organization_id,

cic.inventory_item_id

),

0

),

0, -1,

apps.xxcofi_mer_sold_not_del_pkg.getonhandqty

(cic.organization_id,

cic.inventory_item_id

)

)

),

'-'

),

0, DECODE (INSTR (osmv.meaning, 'Home'),

0, '1.1',

'2.1'

),

DECODE (INSTR (osmv.meaning, 'Home'),

0, '1.2',

'2.2'

)

),

1.2, 'Customer Not Pick Up (On Order)',

2.2, 'Home Delivry (On Order)',

NULL

) on_order,

DECODE (SUBSTR (loc.tax_name, 1, 3),

'Bay', 'Bay',

'Zel', 'Zellers',

'KMT', 'Kmart'

) banner,

hr_sold.NAME STORE,

mic.segment4 CATEGORY,

mic.segment1 gma,

mic.segment2 group_name,

mic.segment3 dept,

msib.segment1 item,

msib.description item_description,

pov.segment1 vendor_number,

pov.vendor_name vendor_name,

oh.order_number sales_order_no,

srep.NAME sales_associate,

ol.flow_status_code status,

osmv.meaning shipping_method,

mp.organization_code fulfillment_location,

acr.receipt_date pos_processing_date,

ol.schedule_ship_date promised_date,

(SELECT SUBSTR (period_name, 5, 6)

'-'

SUBSTR (period_name, 1, 3)

FROM gl_periods

WHERE period_set_name = 'HBC'

AND TRUNC (ol.schedule_ship_date) BETWEEN start_date

AND end_date)

financial_period,

SUM (ol.ordered_quantity) quantity,

msib.list_price_per_unit item_cost,

SUM (ol.ordered_quantity)

* NVL (cic.item_cost, 0)

total_frozen_cost,

SUM (ol.ordered_quantity)

* ol.unit_list_price

total_list_price,

SUM (ol.ordered_quantity)

* ol.unit_selling_price

total_sales_price,

((SUM (ol.ordered_quantity)

* ol.unit_list_price)

- (SUM (ol.ordered_quantity)

* ol.unit_selling_price))

provisional_md

FROM oe_order_headers_all oh,

oe_order_lines_all ol,

mtl_system_items_b msib,

hr_organization_units hr_sold,

hr_locations_all loc,

mtl_item_categories_v mic,

fnd_lookup_values flv,

ar_cash_receipts_all acr,

oe_ship_methods_v osmv,

cst_item_costs cic,

cst_cost_types cct,

mtl_parameters mp,

jtf_rs_salesreps srep,

po_vendors pov,

po_approved_supplier_list sl

WHERE oh.header_id = ol.header_id

AND ol.salesrep_id = srep.salesrep_id(+)

AND srep.org_id = 22

AND cic.cost_type_id = cct.cost_type_id

AND cct.cost_type = 'Frozen'

AND ol.inventory_item_id = cic.inventory_item_id

AND oh.attribute1 = cic.organization_id

AND ol.inventory_item_id = msib.inventory_item_id

AND oh.attribute1 = msib.organization_id

AND ol.inventory_item_id = mic.inventory_item_id

AND oh.attribute1 = mic.organization_id

AND ol.line_category_code = 'ORDER'

AND mic.category_set_id = 1

AND oh.attribute1 = hr_sold.organization_id

AND loc.location_id = hr_sold.location_id

AND ol.ship_from_org_id = mp.organization_id

AND ol.attribute7 IS NOT NULL

AND (ol.attribute7 = acr.cash_receipt_id(+))

AND ol.flow_status_code

IN

('AWAITING_FULFILLMENT', 'AWAITING_SHIPPING', 'PICKED',

'BOOKED', 'PICKED PARTIAL')

AND flv.lookup_type = 'ITEM_TYPE'

AND flv.LANGUAGE = USERENV ('LANG')

AND flv.attribute1 = 'Merchandise'

AND flv.lookup_code = msib.item_type

AND osmv.lookup_code = ol.shipping_method_code

AND ( osmv.meaning

LIKE 'Customer-Pickup%'

OR osmv.meaning

LIKE 'Home Delivery%'

)

AND msib.inventory_item_id = sl.item_id

AND sl.vendor_id = pov.vendor_id

AND sl.attribute5 = 'Primary'

AND TRUNC (NVL (acr.receipt_date , oh.ordered_date)) <= TRUNC (SYSDATE)

AND (sl.disable_flag IS NULL OR sl.disable_flag = 'N')

GROUP BY hr_sold.NAME,

mic.segment4,

msib.segment1,

msib.inventory_item_id,

oh.order_number,

srep.NAME,

ol.flow_status_code,

osmv.meaning,

mp.organization_code,

acr.receipt_date,

ol.schedule_ship_date,

cic.item_cost,

msib.list_price_per_unit,

cic.organization_id,

cic.inventory_item_id,

ol.ordered_quantity,

ol.unit_list_price,

ol.unit_selling_price,

DECODE (SUBSTR (loc.tax_name, 1, 3),

'Bay', 'Bay',

'Zel', 'Zellers',

'KMT', 'Kmart'

),

mic.segment1,

mic.segment2,

mic.segment3,

msib.description,

pov.segment1,

pov.vendor_name

ORDER BY 1, 2, 5, 3, 4;

BEGIN

err_location := 'Error in reading profile variable';

/* l_top contains the outbound path */

fnd_profile.get ('XXCOFIDATA_TOP', l_top);

IF l_top IS NULL OR l_top = ''

THEN

l_top := '/cdv01/oracle/cdvappl/xxcofidata/1.0.0/outbound';

ELSE

l_top := l_top

'/outbound';

END IF;

/********************************************************/

/* Open/create Output to build report in CSV format. */

/********************************************************/

err_location := 'Error in open output file';

v_file_hdl :=

UTL_FILE.fopen (l_top,

report_name

'_'

TO_CHAR (SYSDATE, 'YYYYMMDDHH24MI')

'.csv',

'w'

);

/* Put start message in the concurrent manager log file. */

/************************************************************/

write_log (' ');

write_log ('START RUNNING '

report_name

' REPORT PROGRAM.');

write_log ('-***-');

write_log (' ');

write_log ('Date used in generating this report: '

SYSDATE);

write_log (' ');

/********************************************************/

/* Begin to generate CSV files */

/********************************************************/

write_log ('Outputing '

report_name

'*.csv FILE TO '

l_top);

write_log ('START GENERATING REPORT...');

/* Report Column Header Details */

tempstr :=

'COFI Merchandise Sold Not Delivered Split Report,,,,,,,,,,,,,'

SYSDATE;

write_detail_record (tempstr);

tempstr := 'For Month End:,,'

TO_CHAR (SYSDATE, 'DD-MON-YYYY');

write_detail_record (tempstr);

write_detail_record ( 'In_Stock'

','

'On_Order'

','

'Banner'

','

'Store'

','

'Category'

','

'GMA'

','

'Group_Name'

','

'DEPT'

','

'Item'

','

'Item_Description'

','

'Vendor_Number'

','

'Vendor_Name'

','

'Sales_Order_No'

','

'Sales_Associate'

','

'Status'

','

'Shipping_Method'

','

'Fulfillment_Location'

','

'POS_Processing_Date'

','

'Promised_Date'

','

'Financial_Period'

','

'Quantity'

','

'Item_Cost'

','

'"'

'Total_Frozen_Cost'

'",'

'"'

'Total_List_Price'

'",'

'"'

'Total_Sales_Price'

'",'

'"'

'Provisional_MD'

'"'

);

/* Make record count as zero */

l_record_no := 0;

/* Start writing record column details to UTL File */

err_location := 'Error in retrieving rows from database';

FOR out_rec IN out_record_master

LOOP

err_location := 'Error in writing detail record';

write_detail_record ( out_rec.in_stock

','

out_rec.on_order

','

out_rec.banner

','

out_rec.STORE

','

out_rec.CATEGORY

','

out_rec.gma

','

out_rec.group_name

','

out_rec.dept

','

out_rec.item

','

out_rec.item_description

','

out_rec.vendor_number

','

out_rec.vendor_name

','

out_rec.sales_order_no

','

'"'

out_rec.sales_associate

'"'

','

out_rec.status

','

out_rec.shipping_method

','

out_rec.fulfillment_location

','

TO_CHAR (out_rec.pos_processing_date,

'DD-MON-YYYY'

)

','

TO_CHAR (out_rec.promised_date,

'DD-MON-YYYY')

','

out_rec.financial_period

','

out_rec.quantity

','

'"'

TO_CHAR (out_rec.item_cost,

'$999,999,999.90')

'",'

'"'

TO_CHAR (out_rec.total_frozen_cost,

'$999,999,999.90'

)

'",'

'"'

TO_CHAR (out_rec.total_list_price,

'$999,999,999.90'

)

'",'

'"'

TO_CHAR (out_rec.total_sales_price,

'$999,999,999.90'

)

'",'

'"'

TO_CHAR (out_rec.provisional_md,

'$999,999,999.90'

)

'"'

);

/* Increment the record count */

l_record_no := l_record_no + 1;

END LOOP;

IF l_record_no = 0

THEN

write_log (' ');

write_log ('There are no data available for building the report');

write_log (' ');

write_detail_record (' ');

write_detail_record

('There are no data available for building the report');

write_detail_record (' ');

UTL_FILE.fflush (v_file_hdl);

UTL_FILE.fclose (v_file_hdl);

errbuf := report_name

' REPORT FILE JOB COMPLETED SUCCESSFULLY';

retcode := '0';

/*****************************************************/

/* Output Number Of Record in detail transactions. */

/*****************************************************/

write_log (' ');

write_log ('Total records for report: '

l_record_no);

write_log (' ');

write_log ('JOB COMPLETED WITH NO DATA AVAILABLE');

fnd_file.CLOSE;

-- (FND_FILE.OUTPUT);

RETURN;

END IF;

UTL_FILE.fflush (v_file_hdl);

UTL_FILE.fclose (v_file_hdl);

errbuf := report_name

' REPORT FILE JOB COMPLETED SUCCESSFULLY';

retcode := '0';

/*****************************************************/

/* Output Number Of Record in detail transactions. */

/*****************************************************/

write_log (' ');

write_log ('Total records for report: '

l_record_no);

write_log (' ');

write_log ('JOB COMPLETED SUCCESSFULLY');

fnd_file.CLOSE; -- (FND_FILE.OUTPUT);

EXCEPTION

WHEN UTL_FILE.invalid_filehandle

THEN

UTL_FILE.fclose (v_file_hdl);

errbuf := 'INVALID FILE HANDLE';

retcode := '1';

write_error_record ( err_location

': '

SQLCODE

' AND ERROR is INVALID FILE HANDLE : '

SQLERRM

);

fnd_file.CLOSE;

WHEN UTL_FILE.invalid_path

THEN

UTL_FILE.fclose (v_file_hdl);

errbuf := 'INVALID PATH';

retcode := '2';

write_error_record ( err_location

': '

SQLCODE

' AND ERROR is INVALID PATH : '

SQLERRM

);

fnd_file.CLOSE;

WHEN UTL_FILE.invalid_mode

THEN

UTL_FILE.fclose (v_file_hdl);

errbuf := 'INVALID MODE';

retcode := '3';

write_error_record ( err_location

': '

SQLCODE

' AND ERROR is INVALID MODE : '

SQLERRM

);

fnd_file.CLOSE;

WHEN UTL_FILE.invalid_operation

THEN

UTL_FILE.fclose (v_file_hdl);

errbuf := 'INVALID OPERATION';

retcode := '4';

write_error_record ( err_location

': '

SQLCODE

' AND ERROR is INVALID OPERATION : '

SQLERRM

);

fnd_file.CLOSE;

WHEN UTL_FILE.read_error

THEN

UTL_FILE.fclose (v_file_hdl);

errbuf := 'READ ERROR';

retcode := '5';

write_error_record ( err_location

': '

SQLCODE

' AND ERROR is READ ERROR : '

SQLERRM

);

fnd_file.CLOSE;

WHEN UTL_FILE.write_error

THEN

UTL_FILE.fclose (v_file_hdl);

errbuf := 'WRITE ERROR';

retcode := '6';

write_error_record ( err_location

': '

SQLCODE

' AND ERROR is WRITE ERROR : '

SQLERRM

);

fnd_file.CLOSE;

WHEN UTL_FILE.internal_error

THEN

UTL_FILE.fclose (v_file_hdl);

errbuf := 'INTERNAL ERROR';

retcode := '7';

write_error_record ( err_location

': '

SQLCODE

' AND ERROR is INTERNAL ERROR : '

SQLERRM

);

fnd_file.CLOSE;

WHEN NO_DATA_FOUND

THEN

UTL_FILE.fclose (v_file_hdl);

errbuf := 'NO DATA FOUND';

retcode := '8';

write_error_record ( err_location

': '

SQLCODE

' Error is NO DATA FOUND :'

SQLERRM

);

fnd_file.CLOSE;

WHEN OTHERS

THEN

UTL_FILE.fclose (v_file_hdl);

errbuf := 'OTHERS ';

retcode := '9';

write_error_record (err_location

': '

SQLERRM);

fnd_file.CLOSE;

END;

END xxcofi_mer_sold_not_del_pkg;

/

Customer Conversion Extract

CREATE OR REPLACE PACKAGE APPS.xxcofi_cust_cov_pkg

AS

PROCEDURE get_cust_home_phone (

p_party_id IN NUMBER

,p_home_phone_country_code OUT VARCHAR2

,p_home_phone_area_code OUT VARCHAR2

,p_home_phone_number OUT VARCHAR2

,p_home_phone_extension OUT VARCHAR2

);

PROCEDURE get_cust_office_phone (

p_party_id IN NUMBER

,p_business_phone_country_code OUT VARCHAR2

,p_business_phone_area_code OUT VARCHAR2

,p_business_phone_number OUT VARCHAR2

,p_business_phone_extension OUT VARCHAR2

);

PROCEDURE get_cust_cell_details (

p_party_id NUMBER

,p_cell_phone_country_code OUT VARCHAR2

,p_cell_phone_area_code OUT VARCHAR2

,p_cell_phone_number OUT VARCHAR2

,p_cell_phone_extension OUT VARCHAR2

);

PROCEDURE get_cust_fax_details (

p_party_id IN NUMBER

,p_fax_phone_country_code OUT VARCHAR2

,p_fax_phone_area_code OUT VARCHAR2

,p_fax_phone_number OUT VARCHAR2

,p_fax_phone_extension OUT VARCHAR2

);

PROCEDURE get_cust_gen_details (

p_party_id IN NUMBER

,p_gen_phone_country_code OUT VARCHAR2

,p_gen_phone_area_code OUT VARCHAR2

,p_gen_phone_number OUT VARCHAR2

);

PROCEDURE get_cust_email_details (

p_party_id IN NUMBER

,p_email_address OUT VARCHAR2

);

/*PROCEDURE get_cust_loc_details (

p_party_id IN NUMBER

,p_city OUT VARCHAR2

,p_province OUT VARCHAR2

,p_country OUT VARCHAR2

,p_postal_code OUT VARCHAR2

,p_address_line_1 OUT VARCHAR2

,p_address_line_2 OUT VARCHAR2

,p_address_line_3 OUT VARCHAR2

,p_address_use_type OUT VARCHAR2

);

*/

PROCEDURE get_cust_vm_details (

p_party_id IN NUMBER

,p_vm_phone_country_code OUT VARCHAR2

,p_vm_phone_area_code OUT VARCHAR2

,p_vm_phone_number OUT VARCHAR2

,p_vm_phone_extension OUT VARCHAR2

);

PROCEDURE get_cust_pager_details (

p_party_id IN NUMBER

,p_pager_country_code OUT VARCHAR2

,p_pager_area_code OUT VARCHAR2

,p_pager_phone_number OUT VARCHAR2

,p_pager_extension OUT VARCHAR2

);

PROCEDURE extract_customer_data (p_batch_size IN NUMBER);

PROCEDURE main (errbuf OUT VARCHAR2, retcode OUT VARCHAR2, p_batch_size IN NUMBER);

END xxcofi_cust_cov_pkg;

/

CREATE OR REPLACE PACKAGE BODY APPS.xxcofi_cust_cov_pkg

AS

-- Procedure to get the customer home phone details

PROCEDURE get_cust_home_phone(

p_party_id IN NUMBER

,p_home_phone_country_code OUT VARCHAR2

,p_home_phone_area_code OUT VARCHAR2

,p_home_phone_number OUT VARCHAR2

,p_home_phone_extension OUT VARCHAR2

)

AS

BEGIN

SELECT phone_country_code

,phone_area_code

,phone_number

,phone_extension

INTO p_home_phone_country_code

,p_home_phone_area_code

,p_home_phone_number

,p_home_phone_extension

FROM hz_contact_points

WHERE owner_table_name(+) = 'HZ_PARTIES'

AND owner_table_id = p_party_id

AND status = 'A'

AND primary_flag = 'Y'

AND contact_point_type = 'PHONE'

AND phone_line_type = 'HOME:';

EXCEPTION

WHEN NO_DATA_FOUND

THEN

p_home_phone_country_code := NULL;

p_home_phone_area_code := NULL;

p_home_phone_number := NULL;

p_home_phone_extension := NULL;

fnd_file.put_line(fnd_file.LOG, 'NO Data Found For Customer Home Phone Address for Customer ID:'

p_party_id);

WHEN OTHERS

THEN

p_home_phone_country_code := NULL;

p_home_phone_area_code := NULL;

p_home_phone_number := NULL;

p_home_phone_extension := NULL;

fnd_file.put_line(fnd_file.LOG, '*****'

''

SQLERRM

'*****'

'for'

''

'Customer Home Phone Address ');

END;

-- Procedure to get the customer office phone details

PROCEDURE get_cust_office_phone(

p_party_id IN NUMBER

,p_business_phone_country_code OUT VARCHAR2

,p_business_phone_area_code OUT VARCHAR2

,p_business_phone_number OUT VARCHAR2

,p_business_phone_extension OUT VARCHAR2

)

AS

BEGIN

SELECT phone_country_code

,phone_area_code

,phone_number

,phone_extension

INTO p_business_phone_country_code

,p_business_phone_area_code

,p_business_phone_number

,p_business_phone_extension

FROM hz_contact_points

WHERE owner_table_name(+) = 'HZ_PARTIES'

AND owner_table_id = p_party_id

AND status = 'A'

AND primary_flag = 'Y'

AND contact_point_type = 'PHONE'

AND phone_line_type = 'OFFICE:';

EXCEPTION

WHEN NO_DATA_FOUND

THEN

p_business_phone_country_code := NULL;

p_business_phone_area_code := NULL;

p_business_phone_number := NULL;

p_business_phone_extension := NULL;

fnd_file.put_line(fnd_file.LOG, 'NO Data Found For Customer Business Phone Address for Customer ID:'

p_party_id);

WHEN OTHERS

THEN

p_business_phone_country_code := NULL;

p_business_phone_area_code := NULL;

p_business_phone_number := NULL;

p_business_phone_extension := NULL;

fnd_file.put_line(fnd_file.LOG, '*****'

''

SQLERRM

'*****'

'for'

''

'Business Phone Address ');

END;

-- Procedure to get the customer cell phone details

PROCEDURE get_cust_cell_details(

p_party_id NUMBER

,p_cell_phone_country_code OUT VARCHAR2

,p_cell_phone_area_code OUT VARCHAR2

,p_cell_phone_number OUT VARCHAR2

,p_cell_phone_extension OUT VARCHAR2

)

AS

BEGIN

SELECT phone_country_code

,phone_area_code

,phone_number

,phone_extension

INTO p_cell_phone_country_code

,p_cell_phone_area_code

,p_cell_phone_number

,p_cell_phone_extension

FROM hz_contact_points

WHERE owner_table_name(+) = 'HZ_PARTIES'

AND owner_table_id = p_party_id

AND status = 'A'

AND primary_flag = 'Y'

AND contact_point_type = 'PHONE'

AND phone_line_type = 'MOBILE';

EXCEPTION

WHEN NO_DATA_FOUND

THEN

p_cell_phone_country_code := NULL;

p_cell_phone_area_code := NULL;

p_cell_phone_number := NULL;

p_cell_phone_extension := NULL;

fnd_file.put_line(fnd_file.LOG, 'NO Data Found For Customer Cell Phone Address for Customer ID:'

p_party_id);

WHEN OTHERS

THEN

p_cell_phone_country_code := NULL;

p_cell_phone_area_code := NULL;

p_cell_phone_number := NULL;

p_cell_phone_extension := NULL;

fnd_file.put_line(fnd_file.LOG, '*****'

''

SQLERRM

'*****'

'for'

''

'Cell Phone Address ');

END;

-- Procedure to get the customer fax details

PROCEDURE get_cust_fax_details(

p_party_id IN NUMBER

,p_fax_phone_country_code OUT VARCHAR2

,p_fax_phone_area_code OUT VARCHAR2

,p_fax_phone_number OUT VARCHAR2

,p_fax_phone_extension OUT VARCHAR2

)

AS

BEGIN

SELECT phone_country_code

,phone_area_code

,phone_number

,phone_extension

INTO p_fax_phone_country_code

,p_fax_phone_area_code

,p_fax_phone_number

,p_fax_phone_extension

FROM hz_contact_points

WHERE owner_table_name(+) = 'HZ_PARTIES'

AND owner_table_id = p_party_id ---party_id from main query

AND status = 'A'

AND primary_flag = 'Y'

AND contact_point_type = 'PHONE'

AND phone_line_type = 'FAX';

EXCEPTION

WHEN NO_DATA_FOUND

THEN

p_fax_phone_country_code := NULL;

p_fax_phone_area_code := NULL;

p_fax_phone_number := NULL;

p_fax_phone_extension := NULL;

fnd_file.put_line(fnd_file.LOG, 'NO Data Found For Customer Fax Address for Customer ID:'

p_party_id);

WHEN OTHERS

THEN

p_fax_phone_country_code := NULL;

p_fax_phone_area_code := NULL;

p_fax_phone_number := NULL;

p_fax_phone_extension := NULL;

fnd_file.put_line(fnd_file.LOG, '*****'

''

SQLERRM

'*****'

'for'

''

'Fax Address ');

END;

-- Procedure to get the customer pager details

PROCEDURE get_cust_pager_details(

p_party_id IN NUMBER

,p_pager_country_code OUT VARCHAR2

,p_pager_area_code OUT VARCHAR2

,p_pager_phone_number OUT VARCHAR2

,p_pager_extension OUT VARCHAR2

)

AS

BEGIN

SELECT phone_country_code

,phone_area_code

,phone_number

,phone_extension

INTO p_pager_country_code

,p_pager_area_code

,p_pager_phone_number

,p_pager_extension

FROM hz_contact_points

WHERE owner_table_name(+) = 'HZ_PARTIES'

AND owner_table_id = p_party_id ---party_id from main query

AND status = 'A'

AND primary_flag = 'Y'

AND contact_point_type = 'PHONE'

AND phone_line_type = 'PAGER';

EXCEPTION

WHEN NO_DATA_FOUND

THEN

p_pager_country_code := NULL;

p_pager_area_code := NULL;

p_pager_phone_number := NULL;

p_pager_extension := NULL;

fnd_file.put_line(fnd_file.LOG, 'NO Data Found For Customer Pager Address for Customer ID:'

p_party_id);

WHEN OTHERS

THEN

p_pager_country_code := NULL;

p_pager_area_code := NULL;

p_pager_phone_number := NULL;

p_pager_extension := NULL;

fnd_file.put_line(fnd_file.LOG, '*****'

''

SQLERRM

'*****'

'for'

''

'Pager Address ');

END;

-- Procedure to get the customer gen details

PROCEDURE get_cust_gen_details(

p_party_id IN NUMBER

,p_gen_phone_country_code OUT VARCHAR2

,p_gen_phone_area_code OUT VARCHAR2

,p_gen_phone_number OUT VARCHAR2

)

AS

BEGIN

SELECT phone_country_code

,phone_area_code

,phone_number

INTO p_gen_phone_country_code

,p_gen_phone_area_code

,p_gen_phone_number

FROM hz_contact_points

WHERE owner_table_name(+) = 'HZ_PARTIES'

AND owner_table_id = p_party_id

AND status = 'A'

AND primary_flag = 'Y'

AND contact_point_type = 'PHONE'

AND phone_line_type = 'GEN';

EXCEPTION

WHEN NO_DATA_FOUND

THEN

p_gen_phone_country_code := NULL;

p_gen_phone_area_code := NULL;

p_gen_phone_number := NULL;

fnd_file.put_line(fnd_file.LOG, 'NO Data Found For Customer General Details for Customer ID:'

p_party_id);

WHEN OTHERS

THEN

p_gen_phone_country_code := NULL;

p_gen_phone_area_code := NULL;

p_gen_phone_number := NULL;

fnd_file.put_line(fnd_file.LOG, '*****'

''

SQLERRM

'*****'

'for'

''

'Customer General Details');

END;

-- Procedure to get the customer email details

PROCEDURE get_cust_email_details(

p_party_id IN NUMBER

,p_email_address OUT VARCHAR2

)

AS

BEGIN

SELECT email_address

INTO p_email_address

FROM hz_contact_points

WHERE owner_table_name(+) = 'HZ_PARTIES' AND owner_table_id = p_party_id AND status = 'A' AND primary_flag = 'Y'

AND contact_point_type = 'EMAIL';

EXCEPTION

WHEN NO_DATA_FOUND

THEN

p_email_address := NULL;

fnd_file.put_line(fnd_file.LOG, 'NO Data Found For Customer e-mail Address for Customer ID:'

p_party_id);

WHEN OTHERS

THEN

p_email_address := NULL;

fnd_file.put_line(fnd_file.LOG, '*****'

''

SQLERRM

'*****'

'for'

''

'E-mail Address');

END;

/*

-- Procedure to get the customer locations details

PROCEDURE get_cust_loc_details(

p_party_id IN NUMBER

,p_city OUT VARCHAR2

,p_province OUT VARCHAR2

,p_country OUT VARCHAR2

,p_postal_code OUT VARCHAR2

,p_address_line_1 OUT VARCHAR2

,p_address_line_2 OUT VARCHAR2

,p_address_line_3 OUT VARCHAR2

,p_address_use_type OUT VARCHAR2

)

AS

BEGIN

SELECT ship_loc.city

,ship_loc.province

,ship_loc.country

,ship_loc.postal_code

,ship_loc.address1

,ship_loc.address2

,ship_loc.address3

,DECODE(ship_su.site_use_code, 'BILL_TO', 'Y', 'SHIP_TO', 'N')

INTO p_city

,p_province

,p_country

,p_postal_code

,p_address_line_1

,p_address_line_2

,p_address_line_3

,p_address_use_type

FROM hz_cust_site_uses_all ship_su

,hz_party_sites ship_ps

,hz_locations ship_loc

,hz_cust_acct_sites_all ship_cas

WHERE ship_ps.party_id = p_party_id

AND ship_su.cust_acct_site_id = ship_cas.cust_acct_site_id

AND ship_cas.party_site_id = ship_ps.party_site_id

AND ship_loc.location_id = ship_ps.location_id

AND ROWNUM = 1;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

p_city := NULL;

p_province := NULL;

p_country := NULL;

p_postal_code := NULL;

p_address_line_1 := NULL;

p_address_line_2 := NULL;

p_address_line_3 := NULL;

fnd_file.put_line(fnd_file.LOG, 'NO Data Found For Customer Location Details for Customer ID:'

p_party_id);

WHEN OTHERS

THEN

p_city := NULL;

p_province := NULL;

p_country := NULL;

p_postal_code := NULL;

p_address_line_1 := NULL;

p_address_line_2 := NULL;

p_address_line_3 := NULL;

fnd_file.put_line(fnd_file.LOG, '*****'

''

SQLERRM

'*****'

'for'

''

'Customer Locatation Details');

END;

*/

PROCEDURE get_cust_vm_details(

p_party_id IN NUMBER

,p_vm_phone_country_code OUT VARCHAR2

,p_vm_phone_area_code OUT VARCHAR2

,p_vm_phone_number OUT VARCHAR2

,p_vm_phone_extension OUT VARCHAR2

)

AS

BEGIN

SELECT phone_country_code

,phone_area_code

,phone_number

,phone_extension

INTO p_vm_phone_country_code

,p_vm_phone_area_code

,p_vm_phone_number

,p_vm_phone_extension

FROM hz_contact_points

WHERE owner_table_name(+) = 'HZ_PARTIES'

AND owner_table_id = p_party_id

AND status = 'A'

AND primary_flag = 'Y'

AND contact_point_type = 'PHONE'

AND phone_line_type = 'VM';

EXCEPTION

WHEN NO_DATA_FOUND

THEN

p_vm_phone_country_code := NULL;

p_vm_phone_area_code := NULL;

p_vm_phone_number := NULL;

p_vm_phone_extension := NULL;

fnd_file.put_line(fnd_file.LOG, 'NO Data Found For Customer VM Details for Customer ID:'

p_party_id);

WHEN OTHERS

THEN

p_vm_phone_country_code := NULL;

p_vm_phone_area_code := NULL;

p_vm_phone_number := NULL;

p_vm_phone_extension := NULL;

fnd_file.put_line(fnd_file.LOG, '*****'

''

SQLERRM

'*****'

'for'

''

'Customer VM Details');

END;

PROCEDURE extract_customer_data(

p_batch_size IN NUMBER

)

AS

CURSOR c1_customer_extract

IS

SELECT

hzp.party_id

,hzp.person_first_name

,hzp.person_middle_name

,hzp.salutation

,hzp.person_last_name

,hzp.person_pre_name_adjunct

,hzp.attribute1 privacy_consent_indicator

,hzp.attribute7 last_update_date

,ship_loc.city

,ship_loc.province

,ship_loc.country

,ship_loc.postal_code

,ship_loc.address1

,ship_loc.address2

,ship_loc.address3

,DECODE(ship_su.site_use_code, 'BILL_TO', 'Y', 'SHIP_TO', 'N') address_use_type

FROM hz_parties hzp

,hz_cust_site_uses_all ship_su

,hz_party_sites ship_ps

,hz_locations ship_loc

,hz_cust_acct_sites_all ship_cas

WHERE hzp.party_id=ship_ps.party_id

AND ship_su.cust_acct_site_id = ship_cas.cust_acct_site_id

AND ship_cas.party_site_id = ship_ps.party_site_id

AND ship_loc.location_id = ship_ps.location_id

AND ROWNUM <= p_batch_size

AND NOT EXISTS(SELECT 'X' FROM xxcofi_cust_outbound_tbl xxcot

WHERE hzp.party_id = xxcot.customer_id);

v_home_country_code hz_contact_points.phone_country_code%TYPE;

v_home_area_code hz_contact_points.phone_area_code%TYPE;

v_home_phone_number hz_contact_points.phone_number%TYPE;

v_home_phone_extension hz_contact_points.phone_extension%TYPE;

v_business_country_code hz_contact_points.phone_country_code%TYPE;

v_business_area_code hz_contact_points.phone_area_code%TYPE;

v_business_phone_number hz_contact_points.phone_number%TYPE;

v_business_phone_extension hz_contact_points.phone_extension%TYPE;

v_cell_country_code hz_contact_points.phone_country_code%TYPE;

v_cell_area_code hz_contact_points.phone_area_code%TYPE;

v_cell_phone_number hz_contact_points.phone_number%TYPE;

v_cell_phone_extension hz_contact_points.phone_extension%TYPE;

v_fax_country_code hz_contact_points.phone_country_code%TYPE;

v_fax_area_code hz_contact_points.phone_area_code%TYPE;

v_fax_phone_number hz_contact_points.phone_number%TYPE;

v_fax_phone_extension hz_contact_points.phone_extension%TYPE;

v_gen_country_code hz_contact_points.phone_country_code%TYPE;

v_gen_area_code hz_contact_points.phone_area_code%TYPE;

v_gen_phone_number hz_contact_points.phone_number%TYPE;

v_city hz_locations.city%TYPE;

v_province hz_locations.province%TYPE;

v_country hz_locations.country%TYPE;

v_postal_code hz_locations.postal_code%TYPE;

v_address_line_1 hz_locations.address1%TYPE;

v_address_line_2 hz_locations.address2%TYPE;

v_address_line_3 hz_locations.address3%TYPE;

v_email_address hz_contact_points.email_address%TYPE;

v_vm_number VARCHAR2(200) := NULL;

v_gen_number VARCHAR2(200) := NULL;

v_pager_number VARCHAR2(200) := NULL;

v_vm_country_code hz_contact_points.phone_country_code%TYPE;

v_vm_area_code hz_contact_points.phone_area_code%TYPE;

v_vm_phone_number hz_contact_points.phone_number%TYPE;

v_vm_phone_extension hz_contact_points.phone_extension%TYPE;

v_customer_id hz_parties.party_id%TYPE;

v_pager_country_code hz_contact_points.phone_country_code%TYPE;

v_pager_area_code hz_contact_points.phone_area_code%TYPE;

v_pager_phone_number hz_contact_points.phone_number%TYPE;

v_pager_extension hz_contact_points.phone_extension%TYPE;

v_address_use_type VARCHAR2(1) := NULL;

BEGIN

FOR rec_customer_extract IN c1_customer_extract

LOOP

--- GET THE CUSTOMER HOME PHONE DETAILS

v_customer_id := rec_customer_extract.party_id;

DBMS_OUTPUT.put_line('shekhar'

rec_customer_extract.party_id);

BEGIN

get_cust_home_phone(v_customer_id, v_home_country_code, v_home_area_code, v_home_phone_number, v_home_phone_extension);

END;

--- GET THE CUSTOMER BUSINESS PHONE DETAILS

BEGIN

get_cust_office_phone(v_customer_id, v_business_country_code, v_business_area_code, v_business_phone_number, v_business_phone_extension);

END;

--- GET THE CUSTOMER CELL PHONE DETAILS

BEGIN

get_cust_cell_details(v_customer_id, v_cell_country_code, v_cell_area_code, v_cell_phone_number, v_cell_phone_extension);

END;

--- GET THE CUSTOMER FAX DETAILS

BEGIN

get_cust_fax_details(v_customer_id, v_fax_country_code, v_fax_area_code, v_fax_phone_number, v_fax_phone_extension);

END;

BEGIN

--- GET THE CUSTOMER GEN DETAILS

get_cust_gen_details(v_customer_id, v_gen_country_code, v_gen_area_code, v_gen_phone_number);

END;

BEGIN

--- GET THE EMAIL ADDRESS

get_cust_email_details(v_customer_id, v_email_address);

END;

/*BEGIN

--- GET THE LOCATION ADDRESS

get_cust_loc_details(v_customer_id

,v_city

,v_province

,v_country

,v_postal_code

,v_address_line_1

,v_address_line_2

,v_address_line_3

,v_address_use_type

);

END;*/

BEGIN

-- GET CUSTOMER VM DETAILS

get_cust_vm_details(v_customer_id, v_vm_country_code, v_vm_area_code, v_vm_phone_number, v_vm_phone_extension);

END;

BEGIN

get_cust_pager_details(v_customer_id, v_pager_country_code, v_pager_area_code, v_pager_phone_number, v_pager_extension);

END;

v_vm_number :=(v_vm_country_code

v_vm_area_code

v_vm_phone_number

v_vm_phone_extension);

v_gen_number :=(v_gen_country_code

v_gen_area_code

v_gen_phone_number);

v_pager_number :=(v_pager_country_code

v_pager_area_code

v_pager_phone_number

v_pager_extension);

-- v_fax_number := (v_fax_phone_country_code

v_fax_phone_area_code

v_fax_phone_number);

-- v_cell_off_phone_number := (v_cell_phone_country_code

v_cell_phone_area_code

v_cell_phone_number);

BEGIN

INSERT INTO xxcofi_cust_outbound_tbl

(customer_id

,person_first_name

,person_middle_name

,person_last_name

,salutation

,privacy_consent_indicator

,last_update_date

,last_updated_by

,language_flag

,home_country_code

,home_area_code

,home_phone_number

,home_phone_extension

,business_country_code

,business_area_code

,business_phone_number

,business_phone_extension

,cell_country_code

,cell_phone_areacode

,cell_phone_number

,cell_extension

,fax_country_code

,fax_area_code

,fax_number

,fax_extension

,pager_number

,vm_number

,gen_number

,address_line_1

,address_line_2

,city

,province

,country

,postal_code

,address_use_type

,email_address

)

VALUES (rec_customer_extract.party_id

,rec_customer_extract.person_first_name

,rec_customer_extract.person_middle_name

,rec_customer_extract.person_last_name

,rec_customer_extract.salutation

,rec_customer_extract.privacy_consent_indicator

,SYSDATE

,fnd_global.user_id

,'E'

,v_home_country_code

,v_home_area_code

,v_home_phone_number

,v_home_phone_extension

,v_business_country_code

,v_business_area_code

,v_business_phone_number

,v_business_phone_extension

,v_cell_country_code

,v_cell_area_code

,v_cell_phone_number

,v_cell_phone_extension

,v_fax_country_code

,v_fax_area_code

,v_fax_phone_number

,v_fax_phone_extension

,v_pager_number

,v_vm_number

,v_gen_number

,rec_customer_extract.address1

,rec_customer_extract.address2

,rec_customer_extract.city

,rec_customer_extract.province

,rec_customer_extract.country

,rec_customer_extract.postal_code

,rec_customer_extract.address_use_type

,v_email_address

);

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line(fnd_file.LOG, '*****'

''

SQLERRM

'*****'

'for'

''

'Inserting into custom table');

DBMS_OUTPUT.put_line('*****'

''

SQLERRM

'*****'

'for'

''

'Inserting into custom table');

END;

END LOOP;

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line(fnd_file.LOG, SQLERRM);

END extract_customer_data;

PROCEDURE main(

errbuf OUT VARCHAR2

,retcode OUT VARCHAR2

,p_batch_size IN NUMBER

)

AS

/*****************************************************************************************/

--- Procedure to create customer data extract

--- This procedure populates the custom table

--- and then creates the extract from the custom table

/*****************************************************************************************/

report_name VARCHAR2(70) := 'xxcofi_customer_conversion_program';

v_file_dc UTL_FILE.file_type;

buff_size NUMBER := 24576;

l_record_no NUMBER := 0;

l_top VARCHAR2(200) := NULL;

l_date VARCHAR2(60) := NULL;

l_mode VARCHAR2(5) := NULL;

CURSOR out_cust_data_c1

IS

SELECT customer_id

,person_first_name

,person_middle_name

,person_last_name

,salutation

,privacy_consent_indicator

,last_update_date

,last_updated_by

,language_flag

,home_country_code

,home_area_code

,home_phone_number

,home_phone_extension

,business_country_code

,business_area_code

,business_phone_number

,business_phone_extension

,cell_country_code

,cell_phone_areacode

,cell_phone_number

,cell_extension

,fax_country_code

,fax_area_code

,fax_number

,fax_extension

,pager_number

,vm_number

,gen_number

,address_line_1

,address_line_2

,city

,province

,country

,postal_code

,address_use_type

,email_address

FROM xxcofi_cust_outbound_tbl

WHERE extract_flag IS NULL;

BEGIN

xxcofi_cust_cov_pkg.extract_customer_data(p_batch_size);

---For Populating staging table

--******************************************************************************

-- CREATE OUTPUT FILE IN txt FORMAT

fnd_profile.get('XXCOFIDATA_OUT', l_top);

l_date := TO_CHAR(SYSDATE, 'YYYYMMDDHH24MI');

fnd_file.put_line(fnd_file.LOG, 'START RUNNING '

report_name

'_SS'

' REPORT PROGRAM.');

fnd_file.put_line(fnd_file.output, RPAD('HUDSON''S BAY COMPANY', 50)

'DATE:'

SYSDATE);

fnd_file.put_line(fnd_file.output, RPAD('COFI CUSTOMER DATA EXTRACTION REPORT', 50)

'TIME:'

TO_CHAR(SYSDATE, 'HH24:MI:SS'));

fnd_file.put_line(fnd_file.LOG, 'Outputing '

report_name

'_'

l_date

'.csv FILE TO '

l_top);

v_file_dc := UTL_FILE.fopen(l_top, 'customer.dat', 'W', buff_size);

-- PUT START MESSAGE IN THE LOG FILE

FOR out_cust_data IN out_cust_data_c1

LOOP

l_record_no := l_record_no + 1;

UTL_FILE.put_line(v_file_dc

, RPAD(NVL(TO_CHAR(out_cust_data.customer_id), ' '), 10)

RPAD(NVL(out_cust_data.person_first_name, ' '), 40)

RPAD(NVL(out_cust_data.person_last_name, ' '), 40)

RPAD(NVL(out_cust_data.person_middle_name, ' '), 34)

RPAD(NVL(out_cust_data.salutation, ' '), 4)

RPAD(NVL(out_cust_data.privacy_consent_indicator, ' '), 1)

rpad(to_char(out_cust_data.last_update_date,'DD-MM-YY'),8)

RPAD(NVL(out_cust_data.last_updated_by, ' '), 34)

RPAD(NVL(out_cust_data.language_flag, ' '), 2)

RPAD(NVL(out_cust_data.home_country_code

out_cust_data.home_area_code

out_cust_data.home_phone_number, ' '), 40)

RPAD(NVL(out_cust_data.home_phone_extension, ' '), 20)

RPAD(NVL(out_cust_data.business_country_code

out_cust_data.business_area_code

out_cust_data.business_phone_number

,' '

)

,40

)

RPAD(NVL(out_cust_data.business_phone_extension, ' '), 20)

RPAD(NVL(out_cust_data.cell_country_code

out_cust_data.cell_phone_areacode

out_cust_data.cell_phone_number, ' ')

,40

)

RPAD(NVL(out_cust_data.cell_extension, ' '), 20)

RPAD(NVL(out_cust_data.fax_country_code

out_cust_data.fax_area_code

out_cust_data.fax_number, ' '), 40)

RPAD(NVL(out_cust_data.fax_extension, ' '), 20)

RPAD(NVL(out_cust_data.pager_number, ' '), 40)

RPAD(NVL(out_cust_data.vm_number, ' '), 40)

RPAD(NVL(out_cust_data.gen_number, ' '), 40)

RPAD(NVL(out_cust_data.address_line_1, ' '), 153)

RPAD(NVL(out_cust_data.address_line_2, ' '), 87)

RPAD(NVL(out_cust_data.city, ' '), 35)

RPAD(NVL(out_cust_data.province, ' '), 22)

RPAD(NVL(out_cust_data.country, ' '), 2)

RPAD(NVL(out_cust_data.postal_code, ' '), 12)

RPAD(NVL(out_cust_data.address_use_type, ' '), 10)

RPAD(NVL(out_cust_data.email_address, ' '), 57)

);

fnd_file.put_line(fnd_file.output

, out_cust_data.customer_id

','

out_cust_data.person_first_name

','

out_cust_data.person_middle_name

','

out_cust_data.person_last_name

','

out_cust_data.salutation

','

out_cust_data.privacy_consent_indicator

','

out_cust_data.last_update_date

','

out_cust_data.last_updated_by

','

out_cust_data.language_flag

','

out_cust_data.home_country_code

','

out_cust_data.home_area_code

','

out_cust_data.home_phone_number

','

out_cust_data.home_phone_extension

','

out_cust_data.business_country_code

','

out_cust_data.business_area_code

','

out_cust_data.business_phone_number

','

out_cust_data.business_phone_extension

','

out_cust_data.cell_country_code

','

out_cust_data.cell_phone_areacode

','

out_cust_data.cell_phone_number

','

out_cust_data.cell_extension

','

out_cust_data.fax_country_code

','

out_cust_data.fax_area_code

','

out_cust_data.fax_number

','

out_cust_data.fax_extension

','

out_cust_data.pager_number

','

out_cust_data.vm_number

','

out_cust_data.gen_number

','

out_cust_data.address_line_1

','

out_cust_data.address_line_2

','

out_cust_data.city

','

out_cust_data.province

','

out_cust_data.country

','

out_cust_data.postal_code

','

out_cust_data.address_use_type

','

out_cust_data.email_address

);

BEGIN

UPDATE xxcofi_cust_outbound_tbl

SET extract_flag = 'Y'

WHERE customer_id = out_cust_data.customer_id;

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line(fnd_file.LOG, 'Update failed for Customer ID:'

out_cust_data.customer_id);

END;

END LOOP;

COMMIT;

fnd_file.put_line(fnd_file.LOG, 'Number of Records created to customer File'

' : '

l_record_no);

--------------------------------------------------------------------------------

fnd_file.put_line(fnd_file.output, ' ');

--------------------------------------------------------------------------------

-- BEGINNING OF ERROR CUSTOMER CONVERSION REPORT

--------------------------------------------------------------------------------

-- CREATE THE TRAILER RECORD FOR OUTPUT REPORT FILE

UTL_FILE.fflush(v_file_dc);

UTL_FILE.fclose(v_file_dc);

--******************************************************************************

errbuf := report_name

' REPORT FILE JOB COMPLETED SUCCESSFULLY';

retcode := '0';

-- Output Number Of Record Output

fnd_file.put_line(fnd_file.LOG, '------------------*********************************************----------------+');

fnd_file.put_line(fnd_file.LOG, 'customer extraction Report ');

fnd_file.put_line(fnd_file.LOG, ' ');

fnd_file.put_line(fnd_file.LOG, 'JOB COMPLETED SUCCESSFULLY');

-- FND_FILE.CLOSE;

EXCEPTION

WHEN UTL_FILE.invalid_filehandle

THEN

UTL_FILE.fclose(v_file_dc);

errbuf := 'INVALID FILE HANDLE';

retcode := '1';

fnd_file.put_line(fnd_file.LOG, SQLCODE

' AND ERROR is INVALID FILE HANDLE : '

SQLERRM);

fnd_file.CLOSE;

WHEN UTL_FILE.invalid_path

THEN

UTL_FILE.fclose(v_file_dc);

errbuf := 'INVALID PATH';

retcode := '2';

fnd_file.put_line(fnd_file.LOG, SQLCODE

' AND ERROR is INVALID PATH : '

SQLERRM);

fnd_file.CLOSE;

WHEN UTL_FILE.invalid_mode

THEN

UTL_FILE.fclose(v_file_dc);

errbuf := 'INVALID MODE';

retcode := '3';

fnd_file.put_line(fnd_file.LOG, SQLCODE

' AND ERROR is INVALID MODE : '

SQLERRM);

fnd_file.CLOSE;

WHEN UTL_FILE.invalid_operation

THEN

UTL_FILE.fclose(v_file_dc);

errbuf := 'INVALID OPERATION';

retcode := '4';

fnd_file.put_line(fnd_file.LOG, SQLCODE

' AND ERROR is INVALID OPERATION : '

SQLERRM);

fnd_file.CLOSE;

WHEN UTL_FILE.read_error

THEN

UTL_FILE.fclose(v_file_dc);

errbuf := 'READ ERROR';

retcode := '5';

fnd_file.put_line(fnd_file.LOG, SQLCODE

' AND ERROR is READ ERROR : '

SQLERRM);

fnd_file.CLOSE;

WHEN UTL_FILE.write_error

THEN

UTL_FILE.fclose(v_file_dc);

errbuf := 'WRITE ERROR';

retcode := '6';

fnd_file.put_line(fnd_file.LOG, SQLCODE

' AND ERROR is WRITE ERROR : '

SQLERRM);

fnd_file.CLOSE;

WHEN UTL_FILE.internal_error

THEN

UTL_FILE.fclose(v_file_dc);

errbuf := 'INTERNAL ERROR';

retcode := '7';

fnd_file.put_line(fnd_file.LOG, SQLCODE

' AND ERROR is INTERNAL ERROR : '

SQLERRM);

fnd_file.CLOSE;

WHEN NO_DATA_FOUND

THEN

UTL_FILE.fclose(v_file_dc);

errbuf := 'NO DATA FOUND';

retcode := '8';

fnd_file.put_line(fnd_file.LOG, SQLCODE

' Error is NO DATA FOUND :'

SQLERRM);

fnd_file.CLOSE;

WHEN OTHERS

THEN

UTL_FILE.fclose(v_file_dc);

errbuf := 'OTHERS ';

retcode := '9';

fnd_file.put_line(fnd_file.LOG, SQLCODE

' Error is OTHERS : '

SQLERRM);

fnd_file.CLOSE;

END main;

END xxcofi_cust_cov_pkg;

/

select line,text from user_errors where upper(name) like'XXCOFI_CUST_COV_PKG'

Chandra Sekhar Kadali - Oracle Apps Knowledge Sharing

Visitors