Friday, June 22, 2012

FNDLOAD to add a program to existing request group

FNDLOAD apps/london01 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct XX_WIP_UK_01.ldt REQUEST_GROUP REQUEST_GROUP_NAME="XX_WIP_UK" UNIT_APP="XXWIP" UNIT_NAME="XXWIP4962_01"




FNDLOAD apps/$pwd 0 Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct XX_WIP_UK.ldt

Wednesday, June 6, 2012

Definition Of Bill Of Lading

Definition of 'Bill Of Lading'


A legal document between the shipper of a particular good and the carrier detailing the type, quantity and destination of the good being carried. The bill of lading also serves as a receipt of shipment when the good is delivered to the predetermined destination. This document must accompany the shipped goods, no matter the form of transportation, and must be signed by an authorized representative from the carrier, shipper and receiver.

For example, suppose that a logistics company must transport gasoline from a plant in Texas to a gas station in Arizona via heavy truck. A plant representative and the driver would sign the bill of lading after the gas is loaded onto the truck. Once the gasoline is delivered to the gas station in Arizona, the truck driver must have the clerk at the station sign the document as well.

SQL Query for WIP JOBS associated to a sales order

Query1:
--------
SELECT


ooh.order_number

,msib.segment1

,mr.reservation_quantity

, we.wip_entity_name wip_job_name

, wdj.scheduled_start_date wip_start_date

, wdj.scheduled_completion_date wip_completion_date

, wdj.attribute10 job_type

FROM oe_order_headers_all ooh

, oe_order_lines_all ool

, mtl_reservations mr

, wip_discrete_jobs wdj

, wip_entities we

,mtl_system_items_b msib

WHERE ooh.header_id = ool.header_id

AND ooh.org_id = 160

-- AND ool.item_type_code IN ('CONFIG', 'STANDARD')

AND mr.demand_source_line_id = ool.line_id

AND mr.supply_source_type_id = 5

AND mr.supply_source_header_id = we.wip_entity_id

AND we.wip_entity_id = wdj.wip_entity_id

AND ool.ship_from_org_id = we.organization_id

AND ool.ship_from_org_id=msib.organization_id

AND mr.inventory_item_id=msib.inventory_item_id

AND we.organization_id = wdj.organization_id

Price adjustment query

Query1:
------

select ooh.order_number,


(SELECT SUM(NVL(ordered_quantity,0)*NVL(unit_selling_price,0))

FROM oe_order_lines_all

where header_id=ool.header_id

AND line_id=ool.line_id

and org_id=ool.org_id

)line_total,

SUM(NVL (ool.tax_value, 0)) tax_amount

,(SELECT NVL(SUM(NVL(opa.operand,0)),0)

FROM oe_price_adjustments opa

WHERE

opa.header_id = ooh.header_id

AND opa.line_id=ool.line_id

AND opa.applied_flag = 'Y'

AND opa.list_line_type_code = 'FREIGHT_CHARGE') Freight_charges,

(SELECT NVL(SUM(NVL (ool.pricing_quantity, 0) * NVL (opa.adjusted_amount, 0)),0)

FROM oe_price_adjustments opa

WHERE

opa.header_id = ool.header_id

AND opa.line_id=ool.line_id

AND opa.applied_flag = 'Y'

AND opa.list_line_type_code = 'DIS') Discount_amount

from oe_order_headers_all ooh,

oe_order_lines_all ool,

hz_cust_site_uses_all hcsua,

hz_cust_acct_sites_all hcasa,

hz_cust_accounts hca,

hz_parties hp,

ra_terms rt

,oe_transaction_types_tl ott

,ra_salesreps_all rs

-- ,oe_price_adjustments_v opa

where ooh.header_id=ool.header_id

AND ooh.org_id=ool.org_id

AND ooh.ship_to_org_id = hcsua.site_use_id

AND hcsua.cust_acct_site_id=hcasa.cust_acct_site_id

and hcasa.cust_account_id=hca.cust_account_id

and hca.party_id=hp.party_id

and hcsua.org_id=ooh.org_id

and ooh.payment_term_id=rt.term_id(+)

AND ooh.order_type_id = ott.transaction_type_id

AND ooh.salesrep_id=rs.salesrep_id(+)

AND ooh.org_id=rs.org_id

--AND opa.header_id(+) = ooh.header_id

--AND opa.applied_flag(+) = 'Y'

--AND opa.list_line_type_code(+) = 'FREIGHT_CHARGE'

AND ott.language='US'

AND ool.item_type_code <> 'CONFIG'

AND ool.top_model_line_id is null

--and ooh.order_number='3303592'

AND ooh.org_id=160

--and ooh.creation_date>sysdate-5

AND EXISTS(SELECT 1

FROM MTL_DESCR_ELEMENT_VALUES_V

WHERE inventory_item_id=ool.inventory_item_id

AND element_name='BM.Product'

AND element_value is not null)

group by

hcsua.attribute15,

ooh.transactional_Curr_code,

ooh.cust_po_number,

ooh.fob_point_code,

ool.tax_code,

rt.name,

ool.line_id,

hp.party_name,

hca.account_number,

ooh.order_number

,ott.name

,ooh.flow_status_code

,ool.flow_status_code

,rs.name

,ooh.orig_sys_document_ref

,ool.ordered_quantity

,unit_selling_price

,ool.header_id

,ool.org_id

,ool.pricing_quantity

, ooh.header_id

,ool.ordered_item

,ooh.request_date

,ool.request_date

,ool.schedule_ship_date

,ool.promise_date


Query2:
--------
select ooh.order_number "Order Number"


,hcsua.attribute15 "Ship To Reference Number",

ooh.transactional_Curr_code,

ooh.cust_po_number,

ooh.fob_point_code,

ooh.request_date,

ool.request_date,

ool.schedule_ship_date,

ool.promise_date,

ool.tax_code,

ool.ordered_item,

rt.name,

hp.party_name "Customer Name",

hca.account_number "Customer Number"

,ott.name "Transaction Type Name"

,ooh.flow_status_code "Order Status"

,ool.flow_status_code "Line Status"

,rs.name "Sales Person Name"

,ooh.orig_sys_document_ref

,ool.ordered_quantity

,(select SUM(NVL(ordered_quantity,0)*NVL(unit_selling_price,0)) from oe_order_lines_all where top_model_line_id=ool.top_model_line_id) Line_total

,(select SUM(NVL (tax_value, 0)) from oe_order_lines_all where top_model_line_id=ool.top_model_line_id) Line_tax_amount

,(select NVL(sum(operand),0) from oe_price_adjustments a,oe_order_lines_all b where a.line_id=b.line_id

and a.list_line_type_code='FREIGHT_CHARGE' and a.applied_flag(+) = 'Y'

and b.top_model_line_id=ool.top_model_line_id) freight_charges

,(select NVL(SUM(NVL (b.pricing_quantity, 0) * NVL (a.adjusted_amount, 0)),0) from oe_price_adjustments a,oe_order_lines_all b where a.line_id=b.line_id

and a.list_line_type_code='DIS' and a.applied_flag(+) = 'Y'

and b.top_model_line_id=ool.top_model_line_id) Discount_amount

,ool.line_id

from oe_order_headers_all ooh,

oe_order_lines_all ool,

hz_cust_site_uses_all hcsua,

hz_cust_acct_sites_all hcasa,

hz_cust_accounts hca,

hz_parties hp,

ra_terms rt

,oe_transaction_types_tl ott

,ra_salesreps_all rs

-- ,oe_price_adjustments_v opa

where ooh.header_id=ool.header_id

AND ooh.org_id=ool.org_id

AND ooh.ship_to_org_id = hcsua.site_use_id

AND hcsua.cust_acct_site_id=hcasa.cust_acct_site_id

and hcasa.cust_account_id=hca.cust_account_id

and hca.party_id=hp.party_id

and hcsua.org_id=ooh.org_id

and ooh.payment_term_id=rt.term_id(+)

AND ooh.order_type_id = ott.transaction_type_id

AND ooh.salesrep_id=rs.salesrep_id(+)

AND ooh.org_id=rs.org_id

AND ott.language='US'

AND ool.item_type_code = 'CONFIG'

--and ooh.order_number='3303308'

AND ooh.org_id=160

group by

hcsua.attribute15,

ooh.transactional_Curr_code,

ooh.cust_po_number,

ooh.fob_point_code,

ool.tax_code,

rt.name,

ool.line_id,

hp.party_name,

hca.account_number,

ooh.order_number

,ott.name

,ooh.flow_status_code

,ool.flow_status_code

,rs.name

,ooh.orig_sys_document_ref

,ool.ordered_quantity

,unit_selling_price

,ool.header_id

,ool.org_id

,ool.pricing_quantity

, ooh.header_id

,ool.ordered_item

,ool.top_model_line_id

,ool.link_to_line_id

,ooh.request_date

,ool.request_date

,ool.schedule_ship_date

,ool.promise_date
 

Friday, June 1, 2012

R12: How to set default template for an XML Publisher request? [ID 1264858.1]

R12: How to set default template for an XML Publisher request? [ID 1264858.1]

PDF output from XML Publisher Reports

How To Print XML Publisher PDF Reports Via The Concurrent Manager [ID 338990.1]
NOTE:333504.1 - How To Print Concurrent Requests in PDF Format