Santoshkumaraddar's Oracle Financial Blog: Apps custom views 4m standard tables

Showing posts with label Apps custom views 4m standard tables. Show all posts

Sunday, 10 January 2016

Find requet_group of specific request or C.P

SELECT

--######################################################

---Find requet_group of specific request or C.P

--######################################################

RG.APPLICATION_ID "Request Group Application ID",

RG.REQUEST_GROUP_ID "Request Group - Group ID",

RG.REQUEST_GROUP_NAME,

RG.DESCRIPTION,

rgu.unit_application_id,

rgu.request_group_id "Request Group Unit - Group ID",

rgu.request_unit_id,

cp.concurrent_program_id,

cp.concurrent_program_name,

cpt.user_concurrent_program_name,

DECODE (rgu.request_unit_type,

'P', 'Program',

'S', 'Set',

rgu.request_unit_type)

"Unit Type"

FROM fnd_request_groups rg,

fnd_request_group_units rgu,

fnd_concurrent_programs cp,

FND_CONCURRENT_PROGRAMS_TL CPT

WHERE rg.request_group_id = rgu.request_group_id

AND rgu.request_unit_id = cp.concurrent_program_id

AND cp.concurrent_program_id = cpt.concurrent_program_id

AND cpt.user_concurrent_program_name = 'Your_Concurrent_Program_Name';

Tuesday, 5 January 2016

VIEW for ap_invoices_interface mandatory and requied columns as APPS.AP_INVOICES_INTERFACE_V

CREATE OR REPLACE FORCE VIEW APPS.AP_INVOICES_INTERFACE_V

--##############################################################################--

--# #--

--# View Name : APPS.AP_INVOICES_INTERFACE_V #--

--# #--

--# Description : #--

--# OFFICE MANDATORY AND REQUIED AP INVOICE INTERFACE COLUMNS #--

--# Initial Verion Created By Last_Update_Date #--

--# -------------- ---------- ---------------- #--

--# 1.0 Santosh 05-JAN-2016 #--

--# #--

--##############################################################################--

SELECT org_id,

request_id,

status,

invoice_type_lookup_code,

source,

invoice_id,

invoice_num,

invoice_date,

vendor_id,

vendor_site_id,

invoice_currency_code,

invoice_amount,

description,

exchange_rate,

terms_name,

attribute_category,

attribute10 dff_type_of_payment,

--attribute1,

attribute2 dff_cpr_number,

attribute3 dff_manual_payment,

attribute4 dff_service_name,

attribute5 dff_number_of_instalments,

attribute6 dff_reason_for_refund,

attribute7 dff_اسم_الوكيل,

attribute8 dff_pensioner_cpr,

attribute9 dff_jv_reference,

creation_date,

created_by,

last_update_date,

last_updated_by,

last_update_login,

gl_date,

po_number,

vendor_num,

vendor_name,

vendor_site_code,

exchange_rate_type,

terms_id,

doc_category_code,

voucher_num,

payment_method_lookup_code,

pay_group_lookup_code,

GROUP_ID

/*###########################################

-----Generic view for ap_invoice_interface

SELECT org_id,

status,

invoice_type_lookup_code,

source,

request_id,

invoice_id,

invoice_num,

invoice_date,

gl_date,

po_number,

vendor_id,

vendor_site_id,

vendor_num,

vendor_name,

vendor_site_code,

invoice_currency_code,

invoice_amount,

description,

exchange_rate,

exchange_rate_type,

terms_id,

terms_name,

creation_date,

created_by,

last_update_date,

last_updated_by,

last_update_login,

attribute_category,

attribute1,

attribute2,

attribute3,

attribute4,

attribute5,

attribute6,

attribute7,

attribute8,

attribute9,

attribute10,

doc_category_code,

voucher_num,

payment_method_lookup_code,

pay_group_lookup_code,

GROUP_ID

FROM apps.ap_invoices_interface

###########################################*/

FROM apps.ap_invoices_interface;

VIEW for ap_invoice_lines_interface Mandatory and Requied columns as APPS.AP_INVOICE_LINES_INTERFACE_V

CREATE OR REPLACE FORCE VIEW APPS.AP_INVOICE_LINES_INTERFACE_V

--##############################################################################--

--# #--

--# View Name : APPS.AP_INVOICE_LINES_INTERFACE_V #--

--# #--

--# Description : #--

--# OFFICE MANDATORY AND REQUIED AP INVOICE LINES INTERFACE COLUMNS #--

--# Initial Verion Created By Last_Update_Date #--

--# -------------- ---------- ---------------- #--

--# 1.0 Santosh 05-JAN-2016 #--

--# #--

--##############################################################################--

SELECT org_id,

invoice_id,

invoice_line_id,

line_number,

line_type_lookup_code,

amount,

description,

dist_code_combination_id,

accounting_date,

distribution_set_id,

distribution_set_name,

dist_code_concatenated,

attribute_category,

attribute1,

attribute2,

attribute3,

attribute4,

attribute5,

assets_tracking_flag,

po_header_id,

po_number,

po_line_id,

po_line_number,

po_line_location_id,

po_shipment_num,

po_distribution_id,

po_distribution_num,

po_unit_of_measure,

inventory_item_id,

item_description,

quantity_invoiced,

ship_to_location_code,

unit_price,

match_option,

final_match_flag,

created_by,

creation_date,

last_updated_by,

last_update_date,

last_update_login,

line_group_number

FROM ap_invoice_lines_interface;

CREATE OR REPLACE FORCE VIEW APPS.XXAP_INVLINE_INTERFACE_V

SELECT aii.org_id,

--##############################################################################--

--# #--

--# View Name : XXAP_INVLINE_INTERFACE_V #--

--# #--

--# Description : #--

--# OFFICE MANDATORY AND REQUIED AP INVOICE AND LINES INTERFACE COLUMNS #--

--# Initial Verion Created By Last_Update_Date #--

--# -------------- ---------- ---------------- #--

--# 1.0 Santosh 05-JAN-2016 #--

--# #--

--##############################################################################--

aii.request_id,

aii.status,

aii.invoice_type_lookup_code,

aii.source,

aii.invoice_id,

aii.invoice_num,

aii.invoice_date,

aii.vendor_id,

aii.vendor_site_id,

aii.invoice_currency_code,

aii.invoice_amount,

aii.description,

aii.exchange_rate,

aii.terms_name,

aii.attribute_category,

aii.attribute2 dff_cpr_number,

aii.attribute3 dff_manual_payment,

aii.attribute4 dff_service_name,

aii.attribute5 dff_number_of_instalments,

aii.attribute6 dff_reason_for_refund,

aii.attribute7 dff_اسم_الوكيل,

aii.attribute8 dff_pensioner_cpr,

aii.attribute9 dff_jv_reference,

aii.creation_date,

aii.created_by,

aii.last_update_date,

aii.last_updated_by,

ali.invoice_id line_invoice_id,

ali.invoice_line_id line_invoice_line_id,

ali.line_number line_line_number,

ali.line_type_lookup_code line_line_type_lookup_code,

ali.amount line_amount,

ali.description line_description,

ali.dist_code_combination_id line_dist_code_combination_id,

ali.distribution_set_id line_distribution_set_id,

ali.distribution_set_name line_distribution_set_name,

ali.dist_code_concatenated line_dist_code_concatenated,

ali.attribute_category line_attribute_category,

ali.attribute1 line_attribute1,

ali.attribute2 line_attribute2,

ali.attribute3 line_attribute3,

ali.attribute4 line_attribute4,

ali.attribute5 line_attribute5,

ali.assets_tracking_flag line_assets_tracking_flag,

ali.po_header_id line_po_header_id,

ali.po_number line_po_number,

ali.po_line_id line_po_line_id,

ali.po_line_number line_po_line_number,

ali.po_line_location_id line_po_line_location_id,

ali.po_shipment_num line_po_shipment_num,

ali.po_distribution_id line_po_distribution_id,

ali.po_distribution_num line_po_distribution_num,

ali.po_unit_of_measure line_po_unit_of_measure,

ali.inventory_item_id line_inventory_item_id,

ali.item_description line_item_description,

ali.quantity_invoiced line_quantity_invoiced,

ali.ship_to_location_code line_ship_to_location_code,

ali.unit_price line_unit_price,

ali.match_option line_match_option,

ali.final_match_flag line_final_match_flag

FROM ap_invoices_interface aii, ap_invoice_lines_interface ali

WHERE aii.invoice_id = ali.invoice_id;